How to count non blank cells?

16Jan

by Best Excel Tutorial Data Analysis

In this article, you will create the formula in Excel for non empty cells. You will learn how to count non blank cells.

Table of Contents

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.

Please find below a simple example for the same:

countif non blank cells simple example

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)

countif formula nonblank

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,”*”)

nonempty 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.

countif formula number

Another formula is: =COUNTIF(range,”<>”)

countif non blank cells another formula

Now the result is good. Number has been counted to the result of formula. This is how to count non blank cells.