[Fixed] Excel COUNT Function Not Working

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.


Download Practice Workbook

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


4 Causes and Possible Solutions When Excel COUNT Function Is Not Working

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.

excel count not working

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


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.

When Cells Containing COUNT Formula Are Formatted as Text

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.

Formula showing as Text in Cell

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.

Selecting General Format to Fix COUNT Function not working in Excel

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.

Using F2 key to Fix COUNT Function not working in Excel

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

Showing Total Number of Sales Reps

Similarly, update the result of cell D17. But 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.

Utilizing Find and Replace Feature to Fix COUNT Function not working in Excel

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.

Fixing COUNT Function not working in Excel

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: [Fixed!] IF Function Is Not Working in Excel (4 Quick Solutions)


2. If 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.

If Calculation Option Is Set as Manual

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

Employing Fill Handle Tool

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.

COUNT function showing incorrect output in Excel

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.

Selecting Automatic Calculation Option to Fix COUNT Function not working in Excel

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

Changing Calculation Option to Fix COUNT Function not working in Excel

Read More: Excel Data Validation Greyed Out (4 Reasons with Solutions)


Similar Readings


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 correspondingly. Follow the image below to count the total Sales Reps.

When Cells Contain Text

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, select 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.

Following the Status Bar to know the Total Count of cells in excel

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.

Applying COUNTA Function to Fix COUNT Function not working in Excel

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

Fixing COUNT Function not working in Excel when Cells Contain Text

Read More: [Fixed!] Merge Cells Button Is Greyed Out in Excel


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.

When Formula Returns Text Values

At this moment, we’ll calculate the total number of days from the Formula Column. For doing 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.

COUNT function giving error result

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.

COUNT function not working because of text output in Excel

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. And the COUNT function also is returning correct results as output in cell D16.

Fixing COUNT Function not working in Excel when Formula Returns Text Values

Read More: [Fixed!] Formulas Are Not Calculating Automatically in Excel


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. Please visit our website, Exceldemy, a one-stop Excel solution provider, to explore more.


Related Articles

Shahriar Abrar Rafid

Shahriar Abrar Rafid

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo