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.

**Table of Contents**hide

## 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))`

**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**

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