Shahriar Abrar Rafid
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.
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.
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. 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.
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: [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.
- 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 a blink of an eye, the output in cell E16 gets changed to 8 from 10.
Read More: Excel Data Validation Greyed Out (4 Reasons with Solutions)
Similar Readings
- [Fixed!] Excel Scroll Bar Too Long (5 Solutions)
- [Fixed!] Unshare Workbook Greyed Out in Excel
- [Fixed] Excel Date Filter Not Working
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.
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.
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.
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.
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.
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. And the COUNT function also is returning correct results as output in cell D16.
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.
