< Go Back

How to Use COUNTA to Count Non-Blank Cells

★ 30 minute read

The Excel function COUNTA is used for counting cells in a selected range or multiple ranges. The COUNTA function counts all types of data in the selected range which are not blank. In this tutorial you will learn how to count non-blank cells in a given range, specific data count, the difference between COUNTA and COUNT functions and alternative way to count text cells in the range.

All of the mentioned techniques are very useful for daily data manipulation in Excel

  1.    Count Non-Blank Cells with COUNTA
  2.    Compare COUNTA and COUNT
  3.    The alternative way to count cells with text

 

Count Non-Blank Cells with COUNTA

If we want to count the number of nonblank cells in a defined range or multiple ranges, we need to use the function COUNTA. Let’s first look at formula syntax and one simple example:

=COUNTA(value1, [value2, value3, …])

Value1 is the obligatory parameter and is sent a range of cells, while other values are not obligatory and can be added as per the user’s needs.

Please note that the COUNTA function counts all cells that contain the following data: number, text, date/time, Boolean (True or False), only spaces, blank cell as a result of a function or functions error values (#N/A, #VALUE or similar).

Here is one example of using COUNTA function:

=COUNTA(B3:B11)

 

In this example we want to count all non-blank cells in range B3:B11. As you can see, we have only one empty cell (B9), while all other cells are counted, including B7 which is Boolean (TRUE) and B8 which is an error (#N/A). As a result, we get 8 non-blank cells in the range.

Sometimes it can be very tricky to visually check the result of the COUNTA function. At the beginning of this topic, we mentioned that the function also counts a blank cell if it is a result of some other function. Let’s look at one example to explain it easier:

E2 = COUNTA(B3:B11)

B10 = IF(B3<>B4,"")

In this example, we put formula =IF(B3<>B4,””) into cell B10 which is part of COUNTA range (B3:B11). The result of this formula is an empty cell value in B10, as B3 value (Mike) is different from B4 value (Jacob). However, as you can see in the second picture, this cell also takes in the count when using COUNTA function, and the result of a function is 8

 

Compare COUNTA and COUNT

While learning the COUNTA function, it is very important to compare it with the COUNT function and highlight the difference between these two. In the following example, we will use both functions on the same range and see differences. In cell E2 we put COUNTA function for range B3:B11, and in cell E4 we put COUNT function for the same range.

As we can see from the example, COUNTA function is returning again 8, the total number of non-blank cells, while on the other side, COUNT function is returning 1, because it takes in count only number values in the selected range, which is, in our case, cell B11 (value 43).

 

The alternative way to count cells with text

Another interesting way to count only cells containing text values in the selected range is to use the combination of SUMPRODUCT and ISTEXT functions. Here is the syntax:

=SUMPRODUCT(--ISTEXT(range))

ISTEXT(range) returns Boolean TRUE or FALSE for every cell in the given range if the cell value contains text. Double hyphen “- -“ turns TRUE to number 1 and FALSE to number 0. The resulting array is the parameter for SUMPRODUCT function, which sums all elements of the array. Let’s look at the example which is the easier way to explain:

=SUMPRODUCT(--ISTEXT(B3:B11))

Function ISTEXT(B3:B11) will return TRUE if a cell contains text, and FALSE if not for all cells in the range. So after ISTEXT is executed, the value of the formula is:

The double hyphen “- -“ will turn all Booleans into 1 or 0, so the formula will have value:

=SUMPRODUCT({1; 1; 1; 1; 0; 0; 0; 0; 0})

SUMPRODUCT will sum all elements of the array, and the final result will be: 4. If we look again at the example, text values are: “Mike”, “Jacob”, “Lynda” and “Lily” which is 4.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

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