Which function would you use if you wanted to count the number of values but ignore cells?

Excel has a variety of ways to count cells in a list, range or data set.  Some are more suitable than others depending on the data types you want to count.  To learn the various methods of counting in Excel read on…

Counting in Excel

There are four key functions for counting in Excel and worthwhile making any Excel users knowledge base.  The four functions to know are:

1. =COUNT
2. =COUNTA
3. =COUNTIF
4. =COUNTBLANK

Let us look at each of these Excel counting functions in more detail to understand when best to use.

The COUNT function in Excel can be used to count the number of cells within a specified data range that contain values.  By Values that means numerical values and not text or strings.  The COUNT function will ignore any text values in the specified range and it will also ignore any blank or empty cells in the range.

For example if you wanted to count the number of values in cells B2 to B11 then you would use:

= COUNT(B2:B11)

And that would produce an answer of 10 because there are 10 values in the specified range:

However if you change a couple of the values in the range, making cell C4 blank and cell C8 equal to ABC then notice the COUNT function does not produce an error, rather it Ignores those two values and produces a result of 8 values in the specified range:

COUNTA

The COUNTA function in Excel can be used to count the number of cells within a specified data range that contain values or text, basically this function will count any cell in the range that is not empty.

If we refer back to our example and change the formula in cell B12 to

=COUNTA(B2:B11)

Then it will produce a result of 9 as there are 9 non-empty cells in the specified data range:

So that covers counting values and counting non-empty cells but what about if you want to count only specified cells, read on!

COUNTIF

The COUNTIF function in Excel can be used to count the number of cells within a specified data range that meet a certain criteria. This is a highly useful function as you can use it to just count specific values, i.e those over 5 and you can even adopt a little trick to count all cells in a range, regardless of whether they are blank, text or values.

Falling back to the current example if you want to count all cells in the range that are over 5 the formula in cell B12 would be:

=COUNTIF(B2:B11,”>5″)

To break that down in more detail the COUNTIF function requires two inputs:

1. The specified data range.  In this example the specified data range is cells B2:B11.
2. The criteria to apply when counting.  After specifying the range a comma is used as a separator and then your criteria is written in quotation marks so in this case because we want only values greater than 5 the criteria is “>5”.

In action this will return a result of 4 in our example, this is because out of the specified data range of B2:B11 only the values 6,8,9 and 10 meet the criteria of being greater than 5:

You can use the COUNTIF function to also just count specified text strings which can come in very handy when performing Excel data analysis.

If in our example we wanted to count how many cells contain the string “ABC” then the formula to use in cell B12 would be:

=COUNTIF(B2:B11, “ABC”)

There is only one cell with the value ABC in the range, as shown by performing this formula on the Excel worksheet:

Another final trick with the COUNTIF function in Excel is that you can use it to count all cells in a range, regardless of whether they are values, text, empty or not.  To do this use the formula below in cell B12:

=COUNTIF(B2:B11,”<>”””)

This formula counts all 10 cells in the range:

COUNTBLANK

The final Excel counting function to introduce you to is COUNTBLANK and as you have probably guessed this can be used to count the number of blank or empty cells in a specified range.

You might question why this function is useful but believe me it is invaluable for performing data analysis and quality checks on data sets.  Sometimes in Excel data analysis it is very important to understand what data is missing and if a record is supposed to have a value but is empty then the COUNTBLANK function can be used to identify those problems.

To use the COUNTBLANK function the formula is very simple.  In cell B12 we simply enter:

=COUNTBLANK(B2:B11)

Again with the example this will produce a result of 1 as there is only 1 blank/empty cell in the specified data range:

Summary

• There are four key counting functions in Excel.
• Use =COUNT for values/numbers
• Use =COUNTA when your data range contains values and text and you want to include both types in the count.
• Use =COUNTIF if you have a criteria to specify, i.e. only values greater than 5 or values that equal “ABC”.
• Use =COUNTBLANK for data quality checks and to find out how many blanks or empty cells are within a range of data.

I hope that proves useful to you in your quest to become a better Excel user.  Excel training that even Count von Count himself would be proud of! 🙂

Keep Excelling,

Which function would you use if you wanted to count the number of values but ignore?

Which function would you use if you wanted to count the number of values but ignore cells that have text or are empty group of answer choices?

Which function would you use if you wanted to count the number of values?

What does average function not ignore?