[Fixed] Excel COUNTA Function Not Working

The Excel’s COUNTA function finds the number of cells in a given range that are not empty. This is very useful when you need to count the number of non-empty cells from a large number of cells. But sometimes the COUNTA function may not be working as expected. In this article, we will discuss the possible reasons behind the “Excel COUNTA function not working” and how to fix that.

How to Fix Excel COUNTA Function Is Not Working: 2 Possible Reasons and Solutions

Here we will discuss 2 possible reasons behind the COUNTA function not working in Excel along with the solutions to fix that. So, let’s start!

1. If COUNTA Function Is Not Working for Blanks Returned by Formulas

Here we have used the IF function in the range D5:D12 to extract numbers greater than 70 and return empty values otherwise.

Now the COUNTA formula in cell F5 returns 8 although there are only 5 non-blank cells in the range specified in the formula.

=COUNTA(D5:D12)

This is because the cells that look empty actually contain empty strings returned by the IF function. The COUNTA function does not consider them empty.
So how will you get the desired result that was expected from the COUNTA function? Well, you can create the following formula using the SUMPRODUCT and LEN functions for that.

=SUMPRODUCT(--(LEN(D5:D12)>0))

Formula Explanation: Here, the LEN function returns the number of characters in each cell in the range D5:D12. As the blank cells have no characters, the output becomes {2;0;2;2;0;2;0;2}. Then the condition LEN(D5:D12)>0 returns {TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE}. The double negatives convert TRUE and FALSE to 1 and 0 respectively. So –(LEN(J5:J12)>0) returns {1;0;1;1;0;1;0;1}. Finally, the SUMPRODUCT function totals them to 5.

You can also create the following formula using the ROWS and COUNTBLANK functions to get the same results. Replace the ROWS function with the COLUMNS function if the count_range is in a single row instead of in a single column.

=ROWS(D5:D12)-COUNTBLANK(D5:D12)

Formula Explanation: Here the ROWS function returns the number of rows in the range D5:D12 i.e. 8. The COUNTBLANK function returns the number of blank cells within that range i.e. 3. So the subtraction 8 – 3 returns 5.

If the count_range contains text also, you can use the COUNT and COUNTIF functions to create the following formula for the same results as follows.

=COUNT(D5:D12)+COUNTIF(D5:D12,"*?")

Formula Explanation: Here, the COUNT function returns the number of cells in the range D5:D12 that contain numbers i.e. 4. Then the COUNTIF function counts the number of cells in that range that contain text strings as the criteria argument “*?” indicates text strings. So it returns 2. Then the final output becomes 4 + 2 = 6.

Note: If you copy formulas that return empty strings and paste them as values, the COUNTA function will still count them as non-empty cells. You can use Find & Replace or Go To Special features to select all empty cells. Then put the cursor in the formula bar and press CTRL + ENTER to convert those cells to actual empty cells.

2. When COUNTA Function Is Not Working for Blanks with Invisible Characters

The COUNTA function may also not be working if the cells that look empty in fact contain invisible characters.

For example, here the COUNTA formula returns 8 although it seems there are only 6 non-empty cells in the range C5:C12 used in the formula.

Because those empty cells contain hidden spaces.

Remove the hidden characters from those cells and the COUNTA formula will return the expected result as follows.

Things To Remember

Apparently, you see a cell is empty, but it may not be empty, i.e. contain hidden characters. This may make you think that the COUNTA function is not working properly. So, you need to make sure those cells are really blank before using the COUNTA function to avoid erroneous results.

Download Practice Workbook

You can download the practice workbook from the download button below.

COUNTA Function Not Working [Fixed].xlsx

Conclusion

Now you know how to fix the issue if the Excel COUNTA function is not working as expected. Do you have any further queries or suggestions? Please let us know in the comment section below. Stay with us and keep learning.

<< Go Back to Excel COUNTA Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!