[Fixed] Excel COUNTA Function Not Working

Reason 1 – COUNTA Function Is Not Working for Blanks Returned by Formulas

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

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.
You can create the following formula using the SUMPRODUCT and LEN functions to overcome this:

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