In this article, you will create the formula in Excel for non empty cells. You will learn how to count non blank cells.
Countif function
Before doing that, I will explain the basics about the COUNTIF function.
The Syntax of the COUNTIF formula is:
=COUNTIF(range, criteria)
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.
Example: Counting Specific Entries
Imagine you have a duty chart for seven days, listing the names of three people assigned to each day. To count how many times “Sam” appears in the list, you would use: =COUNTIF(B2:B8, “Sam”)
In this formula:
- B2:B8 is the range containing the names.
- “Sam” is the criteria specifying which name to count.
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:
=COUNTIF(B2:B8, “Sa*”)
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.
Alternative method
A more reliable way to count non-blank cells, including those with numbers, is to use the COUNTIF function with the criteria “<>”: =COUNTIF(range,”<>”)
In this formula:
- The “<>” operator means “not equal to empty”.
- It counts all cells that are not empty, regardless of whether they contain text or numbers.
Now the result is good. Number has been counted to the result of formula. This is how to count non blank cells.
