Count cells that are not blank

In a given range, we might only be interested in counting those cells that are not blank, while ignoring the blank ones. In such a case, we need the excel COUNTA function.

General formula

=COUNTA(range)

The range here specifies the column or row where the cells that are not blank are located.

Explanation

The excel COUNTA function helps us count those cells that contain any form of information, including the error values or empty texts. The key thing here is to note that we are not counting blank cells.

An example of an empty text is where we enter a formula which returns an empty string. In such a case, the COUNTA function will count it as a non-empty cell.

Example 1: To count the total number of students in a class

Figure 1: How to count cells that are not blank in excel

We want to use the excel COUNTA function to count the number of cells that are not blank in the above datasheet. To do this, proceed as follows:

  •         Specify where you want the answer should be put;
  •         Insert the COUNTA function.

=COUNTA(A2:A12)

Figure 2: How to count cells that are not blank in excel

Then press “Enter”

This will give you the answer below:

Figure 3: How to count cells that are not blank in excel

As you can see, the COUNTA function returns 9, while ignoring all the blank cells within the range.

The use of Excel COUNTA function is essential, as it can help you get the number of cells that are not empty in a quick and easy way.

What if you want to avoid empty strings returned by formulas?

It can be inconveniencing is you use the COUNTA function then get all the cells that are non-blank, even if they contain empty strings returned by formulas. If you want to count those cells that contain empty strings, you can use the Excels SUMPRODUCT function. The syntax for the SUMPRODUCT function is as below:

=SUMPRODUCT(--(LEN(A2:A12)>0))

The LEN function will return a character count of all the cells in the specified range.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar