The Excel COUNTIF() function is used to count cells that meet certain criteria. For example, the function can be used to count cells that are larger than a given value or contain a given string of text. However, it can also be used to count cells that aren’t empty. In this tutorial, you’ll learn how to use the Excel COUNTIF() function to count not blank cells. While Excel provides a dedicated function for this, knowing multiple ways of accomplishing this can be very helpful.
By the end of this tutorial, you’ll have learned the following:
- How to use the COUNTIF() function to count not blank cells in Excel
- How to use the alternative COUNTA() function
- Problems that you might encounter when using COUNTIF() to count not blank cells
Use the Excel COUNTIF() Function to Count Not Blank Cells
How can you count not blank cells using Excel COUNTIF()?
To count cells that are not blank in Excel, you can use the COUNTIF() function using the formula: COUNTIF(range, “<>”). The criteria of “<>” will indicate that we want any cells that aren’t equal to nothing.
The Excel COUNTIF() function is used to count cells that meet specific criteria. In this case, we’re looking for cells that aren’t blank. In order to make this work, we can simply pass in the criteria of "<>", which checks if a cell is not equal to nothing.
Let’s take a look at an example of how this works.
Step 1: Load a Sample Dataset
In the same dataset we’re using above, we have two columns of data. The first stores the regions of sales made and the second stores the amount of sales made. We want to count how often sales were made, meaning how often the cells are not blank in the sales column.
Step 2: Use the COUNTIF() Function to Count Not Blank Cells
Use the formula =COUNTIF(C3:C13, “<>”) to count the number of not blank cells. It’s important to wrap the criteria in double quotes in order to let Excel find the criteria. Not doing this could lead to running into an Excel #NUM! error.
In the following section, we’ll explore how to use the Excel COUNTA() function to count not blank cells.
Using Excel COUNTA() to Count Not Blank Cells
Excel actually provides a dedicated function count not blank cells, the COUNTA() function. The COUNTA() function is explicitly used to count the number of cells that aren’t blank, meaning that they contain values. Let’s take a look at an example of how to use the COUNTA() function.
Step 1: Load a Sample Dataset
We’re using the same dataset as we used in our previous dataset. We want to count how often the cells in column C (in the range of C3:C13) aren’t empty.
Step 2: Use the COUNTA() Function to Count Not Blank Cells
In the example above, we using the formula =COUNTA(C3:C13) to count how many non-blank cells are in the range. The COUNTA() function is more purposeful in specifically counting cells that aren’t blank. While the COUNTIF() function can be used to do this, the COUNTA() function is much more intentional.
In the following section, you’ll about some of the common issues you might run into when counting blank cells using the COUNTIF() function.
Problems When Counting Not Empty Cells Using COUNTIF() in Excel
In this section, we’ll explore one of the main problems you might run into when using the COUNTIF() function to count cells that aren’t blank. In the image below, the value in cell C5 actually contains a number of spaces. While the cell looks like it’s empty, it’s actually not. Because of this, the count of cells that aren’t blank increased from 7 to 8.
While this is technically true, since the cells does contain a value, it’s likely not what you expected. While there’s no immediate value to resolve this, it’s important to be aware of this. In order to fix this issue, you likely need to clean the data, say by using the TRIM() function to clean out extra spaces.
Conclusion
In this tutorial, you learned how to use the Excel COUNTIF() and COUNTA() function to count cells that aren’t blank. In many cases, you might want to use Excel to count values that aren’t missing data. In the examples we followed, we counted regions that had made sales. This can be helpful when we want to calculate an average, in order to make sure that we’re calculating an average correctly.
You first learned how to use the Excel COUNTIF() function to count not blank cells by using the criteria of "<>". From there, you learned how to use the purpose-built COUNTA() function to count not blank cells. Finally, you learned how to identify and address common problems you might encounter when counting not blank cells in Excel.
Additional Resources
To learn more about related topics, check out the tutorials below: