You can use the COUNTA function in Excel to count the number of cells in a range that are not empty.
However, sometimes may want to use the COUNTA function to count cells that are not empty and meet some additional criteria.
You can use the following basic syntax to do so:
=COUNTA(B2:B12)-COUNTIF(B2:B12,"0")
This particular formula counts all cells in the range B2:B12 that are not blank and are not equal to 0.
The following example shows how to use this syntax in practice.
Example: Using COUNTA with Criteria in Excel
Suppose we have the following dataset that shows the points scored by basketball players on various teams:
We can use the following COUNTA function with the COUNTIF function to count the number of cells in the Points column that are not empty and not equal to 0:
=COUNTA(B2:B12)-COUNTIF(B2:B12,"0")
The following screenshot shows how to use this formula in practice:
From the output we can see that there are 8 cells in the Points column that are not empty and not equal to zero.
Here’s what this formula actually did under the hood:
- COUNTA counted 10 blank cells.
- COUNTIF counted 2 cells equal to zero.
- Thus, COUNTA – COUNTIF produced 10 – 2 = 8.
Feel free to replace the value in the COUNTIF function to exclude whichever values you’d like from the COUNTA function.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Count Filtered Rows in Excel
How to Count Duplicates in Excel
How to Count by Group in Excel