Excel COUNTIFS Not Working (7 Causes with Solutions)

We’ll use this dataset to demonstrate what actions to take when the COUNTIFS function doesnâ€™t work properly.

7 Actions for Fixing COUNTIFSÂ Not WorkingÂ

Issue 1 – COUNTIFS Not Working When Counting Text Values

In the following image, we havenâ€™t inserted the text within double quotes. So, the formula has returned 0.

• Use the following corrected formula:
`=COUNTIFS(E5:E12, "Car")`

• Hit Enter.

Issue 2 – COUNTIFS Not Working for an Incorrect Range Reference

We want to count the number of car sellers in Austin in our dataset. So, we have inserted the formula: `=COUNTIFS(E5:E12,"Car",D5:D11,"Austin")`.

The range for the first criteria is E5:E12 but the range for the second criteria is D5:D11. The number of cells in the arrays doesn’t match.

If we press Enter the formula will return the #VALUE! error.

• Correct the formula to:
`=COUNTIFS(E5:E12,"Car",D5:D12,"Austin")`

The number of cells in the range for criteria is the same. So the formula will count the data where Product matches with Car and Region matches with Austin.

• Hit Enter.

Issue 3 – COUNTIFS Not Working for Errors in Formula

We want to find out the number of sales which are more than \$100,000. We have inserted the formula: `=COUNTIFS(F5:F12,">" 100000`). We have only inserted the operator inside of the quotation, not the numeric criteria.

• If we press Enter, a Microsoft Excel message box will appear showing that thereâ€™s a problem with this formula.

• Use the corrected formula:
`=COUNTIFS(F5:F12,">100000")`

We have entered both the operator and the criteria inside quotes.

• Press Enter.

Issue 4 – Counting Based on Values from Other Cells

We want to use the cell I5 as the criteria in the COUNTIFS function. We have typed the following formula, `=COUNTIFS(F5:F12, "< I5 ")`. Here we have directly inserted the cell reference in the formula.

If we press Enter,Â the formula has returned 0.

• Use the following formula,
`=COUNTIFS(F5:F12, "< " &I5)`

We concatenated the value of the reference cell to the criterium string by inserting & before it.

• Press Enter.

Read more: COUNTIF between Two Cell Values in Excel

Issue 5 – COUNTIFS Not Working for OR Logic

The COUNTIFS function can calculate only AND logic but cannot calculate OR logic. We want to get the number of sellers for Car or Motor bike. We have typed the formula, `=COUNTIFS(E5:E12,"Car", E5:E12, "Motor Bike")`. But the formula has returned 0.

• Use the following formula instead:
`=SUM(COUNTIFS(E5:E12,{"Car","Motor Bike"}))`

The COUNTIFS function will return two counts (One for Car, another for Motor Bike) from the array E5:E12 and the SUM function will add up these counts.

• Press Enter.

Issue 6 – Using Wildcards When COUNTIFS Not Working

We have inserted Bike as the criteria in our formula: `=COUNTIFS(E5:E12,"Bike")`. As we have only Motor Bike in our dataset, the COUNTIFS function wonâ€™t work properly and will return 0.

• Use the following formula:
`=COUNTIFS(E5:E12,"*Bike*")`

The check string is between the asterisks (*), so the function will look for partial matches in the range E5:E12.

• Press Enter.

Read more: How to Use COUNTIF with Wildcard in Excel

Issue 7 – COUNTIFS Not Working When Counting from Another Workbook

We have our sales data in sheet Sales of a workbook named Sales Data.

• We want to count the number of car sellers in our current workbook using the data from the Sales Data workbook, which is somewhere else on the PC and not open in Excel. We used the following formula:
`=COUNTIFS('C:\Users\User\Desktop\[Sales Data.xlsx]Automobile'!\$E\$5:\$E\$12, "Car")`

C:\Users\User\Desktop\ indicates the location of the Sales Data workbook and [Sales Data.xlsx]Automobile’!\$E\$5:\$E\$12 indicates the range for criteria from the Sales Data workbook.

• If we press Enter,Â the formula will show #VALUE! error.

• Open the workbook you’re fetching the data from.
• Press F9 to refresh the formula.

<< Go Back toÂ Excel COUNTIFS FunctionÂ |Â Excel FunctionsÂ |Â Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

1. Thank you for this. My problem was in #5. I was trying to use COUNTIFS for the same value in multiple columns. When the value was found, the formula returned 1, even when the value was found in multiple columns. I had forgotten COUNTIFS uses AND logic, not OR.

2. My problem is not any of the above. I have two COUNTIF formulas that works perfectly well =COUNTIF(G5:G452; “Yes”) and =COUNTIF(AO5:AO452; “Yes”) but when combining them =COUNTIFS(G5:G452;”Yes”; AO5:AO452;”Yes”), the result is 0 even if it should be 30 or something. Since COUNTIF works (as well as many other formulas), I don’t believe the problem is in my worksheet.

• Hello, Can you please email us your Excel dataset with the problem?

We will try our best to give you a proper solution.

• I have the same issue, did you find the solution?

• It should count, look in the cell if there is not any space character after the “yes”, if it is “yes “. If any of the cell have a yes with a space it will not count.

• did you manage to get this to work as i have the same issue! Although mine comes up with #value or there are too many arguments =COUNTIFS(I4:I25,”3″, I28:I30,”3″)

• Is not the same issue, the ranges need to be the same lenght.
I4:I25 have 22 cells
I28:I30 have 3 cells

Both ranges need to be the same lenght. ie: I5:I10 and I15:I20 or A1:A10 and B1:B10

3. ITS VERY CLEAR STEP BY explanation . i also faced the same challenge as Adam. Thank for for the hints

4. Hello,
I cannot highlight the criteria e.g f2-f101 in order to get my dataset formula. What can be wrong?

5. Thank you, I would like to mention another typical error. If there is an space character after the word you are trying to count, it’s not exactly the same, so it will not count.
ie:
COUNTIF(A1:A5;”Car”)
will count “Car” or “car” but not “car “. Because of the last space character that is not even visible in the cell.

6. in one column i have vehicle numbers so in another column used formula to get only last 4 digit of number. all this is in Table. now i am trying to count the last 4 digit column but count showing 0

Naimul Hasan Arif Sep 25, 2022 at 2:46 PM

You can apply a formula combining VALUE and RIGHT functions to get the last 4 digits.
For example- you can use the following formula to get the last 4 digits of cell C5.
=VALUE(RIGHT(C5,4))
After that, apply the COUNT function to count the number of cells in that column.
I hope you wil get what you are looking for.

7. Thanks for putting the website together.

I’ve just come across another COUNTIF(S) quirk:

————————————————–
| A | B | C
————————————————–
1 |Count|dimension_name |member_name
————————————————–
2 |0 |MembersBenefitBracketType |<\$1,000
————————————————–

Formulas are placed in cell "A2":
=COUNTIFS(B2:B2,B2,E2:E2,"<\$1000")
=COUNTIF(C2:C2,C2)

Both formulas return zero (0) to cell "A2", rather than 1.

If there is a workaround for this then I'd love to find out what it is.

Advanced Excel Exercises with Solutions PDF