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


Download Practice Workbook

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


2 Possible Reasons and Solutions If Excel COUNTA Function Is Not Working

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.

dataset

  • 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)

Excel COUNTA function not working properly

  • 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))

Excel COUNTA not working - fixed

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)

solution to counta not working in excel

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,"*?")

Alternative solution to COUNTA not working

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.

counta not working as expected

  • Because those empty cells contain hidden spaces.

hidden characters in empty cells

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

remove hidden characters for counta to work properly


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.


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. You can also visit our ExcelDemy blog to explore more about Excel. Stay with us and keep learning.

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo