**The COUNT function** in Excel helps us to count the number of cells containing numerical values within a given range. This is one of the most popular statistical functions in Excel. You may occasionally encounter the problem that the function is not functioning properly. In this article, we’ll introduce you to **4** effective actions that can be taken when **the COUNT function** is not working in Excel. So, let’s be with us.

## Excel COUNT Function Not Working: 4 Causes and Possible Solutions

For ease of understanding, we’re going to use a **Sales Report** of a particular grocery store. This dataset contains the **Sales Rep**, **Order Date**, **Product Name**, and their corresponding **Sales** amount in columns **B**, **C**, **D**, and **E** respectively.

Now, we’ll apply **the COUNT function** in the formula to determine different parameters from the dataset above. Also, we’ll discover the reasons why the formula is not working in the Excel worksheet. and will retrieve potential solutions to these problems. So, let’s explore them one by one.

Here, we have used the *Microsoft Excel 365* version, you may use any other version according to your convenience.

**Read More: **How to Use Different Types of COUNT Functions in Excel

### 1. When Cells Containing COUNT Formula Are Formatted as Text

In this case, we opt to count the **Total Number of Sales Rep** in cell **D16**. To do this, we selected cell **D16** and entered the following formula.

`=COUNT(E5:E14)`

But it doesn’t return the desired output. We can count the **Sales Rep** manually and it’ll result in **10**. But this number isn’t shown in the output cell.

We’re having the same problem with cell **D17**. The reason behind this is the inappropriate formatting of these cells.

- At this time, select cells in the
**D16:D17**range.

You can see that the cells are in **Text** format in the **Number Format** drop-down box in the **Number** group of commands. This is the reason for** the COUNT function** not working in Excel. So, let’s see the steps below to solve this problem.

**📌**** Steps:**

- At the very beginning, select cells in the
**D16:D17**range. - Then, go to the
**Home**tab. - After that, click on the drop-down arrow in the
**Number Format**box. - From the drop-down list, choose
**General**format.

However, the outcome remains the same. The sheet remained unchanged.

To update the output, follow the steps below.

- At first, go to cell
**D16**. - After that, double-click on the cell or press
**F2**on the keyboard. - Then, press
**ENTER**.

Now, we can see the updated and right output in cell **D16**.

Similarly, update the result of cell **D17**. However, doing this is very time-consuming if there are many output cells. How can we overcome it?

You can use the **Find and Replace** feature of Excel to deal with this phenomenon.

- Firstly, press
**CTRL+F**to open the**Find and Replace**wizard. - Here, move to the
**Replace**tab. - Then, write down the equal sign (
**=**) in the**Find what**box. - Also, write down another equal sign (
**=**) in the**Replace with**box. - Lastly, click on the
**Replace All**button.

Immediately, it’ll pop up with the message **“All done. We made 2 replacements”**. Therefore, you can see the two output cells, **D16** and **D17** are exhibiting the correct results.

With this method, you can fix the outputs of various cells at a time, which can save a lot of time and effort.

**Read More: **How to Count Numbers in a Cell in Excel

### 2. If the Calculation Option Is Set as Manual

Now, we’ll talk about our second case of malfunctioning. Here, we want to count the total number of days and the total number of** Sales Rep** by counting the occurrence of the **Sales **amount.

- Firstly, select cell
**D16**and insert the formula below.

`=COUNT(D5:D14)`

- Then, press the
**ENTER**key.

- Later, bring the cursor to the right-bottom corner of cell
**D16**and it’ll look like a plus (**+**) sign. Actually, it’s the**Fill Handle**tool. - Now, drag the tool to cell
**E16**to copy the formula in this cell.

But, it shows an erroneous result. We can count the cells manually in the **Sales** column. There are **8** cells with values. But our formula calculates it as **10**.

Why does it happen? The reason behind it is the action of the **Calculation Option**. Here, the **Calculation Option** is set as **Manual**. So, **Fill Handle** cannot work in this workbook now. So, we have to change it immediately to get rid of this error. Follow the simple steps below.

**📌**** Steps:**

- First of all, jump to the
**Formulas**tab. - Secondly, click on the
**Calculation Options**drop-down on the**Calculation**group of commands. - Thirdly, select
**Automatic**from the options.

In the blink of an eye, the output in cell **E16** gets changed to **8** from **10**.

**Read More: **How to Find 5 Most Frequent Numbers in Excel

### 3. When Cells Contain Text

In this section, we’ll see the inability of this function when calculating the number of text.

Here, we tried to find the **Total Number of Sales Rep** and **Total Number of Products** in cells **D16** and **D17** respectively. Follow the image below to count the total **Sales Reps**.

There are a total of **10** **Sales Reps** in this dataset. But the formula is returning **0** as an output in cell **D16**. Where’s the problem?

There’s no dilemma with our procedure. The main cause behind this matter is that the **COUNT function** ignores text values and all the arguments are text values. So, follow our steps to make it correct.

**📌**** Steps:**

- Initially, selected cells in the
**B5:B14**range that we wanted to count.

Now, look at the **Status Bar** and you will find the total count of **10**.

But the best solution is to use **the COUNTA function**.

- To do this, enter cell
**D16**and insert the following formula.

`=COUNTA(B5:B15)`

- Then, hit the
**ENTER**key.

Equivalently, do the same for the output of cell **D17**.

### 4. When Formula Returns Text Values

The **COUNT function** is also not compatible with the text output of other formulas. Here, we’ll discuss this topic. Let’s see it in action.

Forthwith, using **the IF function**, we want to check which **Order Date** is after the **Preferred Date** and which is not. For the **TRUE** value, we’ll get a return of **1** and **0** for **FALSE**.

- Primarily, select cell
**F5**and paste the formula below.

`=IF(C5>$D$5,“1”,“0”)`

- After that, tap
**ENTER**.

At this moment, we’ll calculate the total number of days from the **Formula Column**. To do this, follow the below tasks.

- First of all, select cell
**D16**and write down the following formula.

`=COUNT(F5:F14)`

- As usual, press the
**ENTER**key.

Here, the result is shown as **0**. But the actual result should be **10**. Why is it happening?

The main reason is the output in cells in the **F5:F14** range. Notice the following image to understand it clearly.

From the above image, we can see that there are double quotes around the digits **1** and **0** in the formula. So, the returned outputs are considered text values. If the formula returns a number value, the **COUNT function** would also work. So, let’s see how we could fix this.

**📌**** Steps:**

- Hence, remove the double quotes from the formula of cell
**F5**.

Now, the outputs in cells in the **F5:F14** range are number values. **The COUNT function** also returns correct results as output in cell **D16**.

You may download the following Excel workbook for better understanding and practice yourself.

## Conclusion

This article explains the probable causes and fixation of the **COUNT function** not working in Excel in a simple and concise manner. Don’t forget to download the **Practice** file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.