### 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 theis in a single row instead of in a single column.*count_range*

`=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
contains text also, you can use the*count_range***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.

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

### Reason 2 – COUNTA Function Is Not Working for Blanks with Invisible Characters

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

- 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

**Download the Practice Workbook**

## Related Articles

- How to Use COUNTA from SUBTOTAL Function in Excel
- Difference Between COUNT and COUNTA Functions in Excel

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