Md. Shamim Reza
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.
- 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))
- 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)
- 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,"*?")
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.
- 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.
Read More: How to Use COUNTA Function with Criteria in Excel (4 Methods)
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.
