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:

**=COUNT****=COUNTA****=COUNTIF****=COUNTBLANK**

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

**COUNT**

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:

**The specified data range.**In this example the specified data range is cells B2:B11.**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,

Pingback: Melanie Glastrong