Excel COUNTIF Not Blank – Formula Syntax & Example
You need to count the number of cells in a range, but you want to exclude the blank ones. The Excel COUNTIF function can do the job easily. Read on to find out how to use COUNTIF not blank in Excel, as well as count blank cells.
Excel COUNTIF cell is not blank syntax
Here is the COUNTIF syntax you should use to count not blank cells in Excel
=COUNTIF(cell-range,"<>")"<>"– a logical operator which corresponds to the “not equal to” condition
Example of Excel formula COUNTIF not blank
For the following example of COUNTIF not blank Excel, we imported data from BigQuery. If you also need to automate the export of queries, use Coupler.io to connect BigQuery to Excel. It’s a tool for integrating different sources, such as Google Sheets or Harvest to Excel.
Learn more about Coupler.io and check out the available Excel integrations that would be useful for your project.
The imported range has a few blank cells.
You can easily count blank cells yourself, however, the number of non-blank cells is more difficult to calculate. This is why you should use the following COUNTIF Excel not blank formula:
=COUNTIF(A2:F10,"<>")
The formula is very helpful if you need to count not blank cells in datasets that are much bigger than in our example. So, feel free to use it for your needs. Now, what about the blank cells, can you count them as well?
Opposite to COUNTIF Excel not blank – formula to calculate blank cells
If you replace the logical operator of the Excel COUNTIF not blank formula with "", you can count the number of blank cells in a range:
=COUNTIF(cell-range,"")Let’s see how it looks in our example dataset:
=COUNTIF(A2:F10,"")
Although this formula works pretty well, the Excel creators decided to provide a dedicated function to count blank cells – COUNTBLANK.
Excel COUNTBLANK to count the number of blank cells
COUNTBLANK counts the number of empty cells in a given range. Here is the syntax:
=COUNTBLANK(cell-range)In our example, the formula will look as follows:
=COUNTBLANK(A2:F10)
Why count blank or not blank cells?
The above mentioned functions are Statistical functions in Excel. They are widely used in statistical analysis and other data manipulation to increase the accuracy of the outcome. Good luck with your data!
