[Fixed] Excel COUNT Function Not Working

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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

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

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 the 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: 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.

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

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


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

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. The COUNT function also returns correct results as output in cell D16.

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


Download Practice Workbook

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.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo