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

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.

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.

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.

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.

Read More: Dynamic Ranges with OFFSET and COUNTA Functions in Excel

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

Read More: How to Use COUNTA Function with Criteria in Excel

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.


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.

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF