In this article we will create the COUNTIF formula in Excel for non empty cells.
Before doing that I will explain the basics about the COUNTIF formula.
The Syntax of the COUNTIF formula is:
So we have 2 arguments to be passed in this:
- Range: It will be the range which we want to be checked for or say the range from which we will count the cells.
- Criteria: It will be the condition which needs to be satisfied for counting the values from the range that we already specified in the range parameter.
Please find below a simple example for the same:
In this example we have the 7 days duty chart for 3 persons.
We have to count the number of duties for a person say "Sam"
And we use the formula as below: =COUNTIF(B2:B8, "Sam")
Here range is: B2:B8
Criteria is : "Sam"
So we the the result as: 3 (because Sam is repeated 3 times under the range)
We can use the same formula to check any name starting with "Sa" like:
Now coming to the point we need to count for non blank cells. We can use: =COUNTIF(range,"*")
As we have only 6 entries, the result is 6 which is correct. But if we use numbers instead of blank it fails because number is treated like blank.
Another formula is: =COUNTIF(range,"<>")
Now the result is good. Number has been counted to the result of formula.
Further reading: How to use Countif function with examples? How to countif cells with partial match? How to use Countifs function?