# Excel COUNTIFS Not Working (7 Causes with Solutions)

Get FREE Advanced Excel Exercises with Solutions!

Excel COUNTIFS function counts values that match one or more criteria from a range. You may sometimes face the issue that the function is not properly working. In this article, I’ll introduce you to 7 actions that can be taken when the COUNTIFS function is not working.

Consider the following dataset. Let’s use this dataset to demonstrate what actions to take when the COUNTIFS function doesn’t work properly. ## 7 Actions to Fixing COUNTIFS Not Working

### 1. COUNTIFS Not Working When Counting Text Values

When we count text strings the text string must be inserted inside of a double quotation mark ( ” “). Otherwise the COUNTIFS function won’t be able to count the text string and will return a value of 0. In the following image, we haven’t inserted the text within double quotation. So the formula has returned 0. Now, to fix this issue,

➤ Type the following corrected formula,

`=COUNTIFS(E5:E12, "Car")`

Now the formula will count the number of the inserted text “Car” from the cell range E5:E12. ➤ Press ENTER

As a result, you will get the desired count. ### 2. COUNTIFS Not Working for Incorrect Range Reference

When we use more than one criteria in the COUNTIFS function, the range of cells for different criteria must have the same number of cells. Otherwise, the COUNTIF function won’t work.

Suppose we want to count the number of car sellers in Austin in our dataset. So, we have typed the formula, `=COUNTIFS(E5:E12,"Car",D5:D11,"Austin")`. If you observe the formula you will see, here 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 range for criteria is not the same. Now, if we press ENTER the formula will return #VALUE! error. Now to fix this error,

➤ Correct the formula by typing,

`=COUNTIFS(E5:E12,"Car",D5:D12,"Austin")`

Here, 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. ➤ Press ENTER

As a result, you will get the number of car sellers in Austin. ### 3. COUNTIFS Not Working for Error in Formula

If we don’t insert the formula correctly, the COUNTIFS function won’t work. When we use any mathematical operator such as greater than (>), less than (<), equal to (=), and not equal to (<>), both the operator and the numerical Criteria must be entered inside of the same quotation. Let’s say we want to find out the number of sales which are more than \$100,000. To find that, we have inserted the formula, `=COUNTIFS(F5:F12,">" 100000`). Here, we have only inserted the operator inside of the quotation, not the numeric criteria. Now, If we press ENTER, a Microsoft Excel message box will appear showing that “ There’s a problem with this formula”. To fix this problem,

➤ Type the corrected formula,

`=COUNTIFS(F5:F12,">100000")`

Now we have entered both the operator and the criteria inside of the quotation. So this time the formula will return the count. ➤ Press ENTER

As a result, you will get the number of sales which are more than \$100,000. ### 4. Counting Based on Values from Other Cell

When we use a cell reference as the criteria of the COUNTIFS function, we must have concatenated the cell reference with the operator by inserting  & before the cell reference. Here only the operator will be in between the quotation marks.

Suppose we want to use the cell I5 as the criteria in the COUNTIFS function. So 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 we will see the formula has returned 0. That means the COUNTIFS function is not working properly and giving wrong values. To fix the problem,

➤ Type the following formula,

`=COUNTIFS(F5:F12, "< " &I5)`

Here, we concatenated the reference cell, I5  by inserting & before it. Now,

➤ Press ENTER,

As a result, the COUNTIFS function will now work and you will get the desired count. Read more: COUNTIF between Two Cell Values in Excel

### 5. COUNTIFS Not Working For OR Logic

The COUNTIFS function can calculate only AND logic but cannot calculate OR logic. So, if you try to get a value using OR logic, the COUNTIFS function won’t work properly. Suppose we want to get the number of sellers of Car or Motor bike. So we have typed the formula, `=COUNTIFS(E5:E12,"Car", E5:E12, "Motor Bike")`. But the formula has returned 0. This is happening because the COUNTIFS function cannot calculate OR logic. By the way, we can use the SUM function and the COUNTIFS function together to calculate OR logic.

➤ Type the following formula,

`=SUM(COUNTIFS(E5:E12,{"Car","Motor Bike"}))`

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

➤ Press ENTER

This time you will get the correct count. ### 6. Using Wildcards When COUNTIFS Not Working

We can use different Wildcards in different conditions when COUNTIFS is not working. For Example, if we want to match partial string from a text string we can use asterisk (*). Suppose we have inserted Bike as the criteria in our formula- `=COUNTIFS(E5:E12,"Bike")`. Now as we have Motor Bike in our dataset, the COUNTIFS function won’t work properly and will return 0. To solve this problem we can use asterisk (*).

➤ Type the following formula,

`=COUNTIFS(E5:E12,"*Bike*")`

As the criteria now are in between the asterisks (*), the function will look for partial matches in the range E5:E12. ➤ Press ENTER,

This time the COUNTIFS will work and will give the correct count. Read more: How to Use COUNTIF with Wildcard in Excel

### 7. COUNTIFS Not Working When Counting from Another Workbook

The COUNTIFS function won’t work if we refer cells from another workbook and the workbook is closed. Suppose we have our sales data in sheet Sales of a workbook named Sales Data. Now, we want to count the number of car sellers in our current workbook using the data from Sales Data workbook. To do that,

➤ Type the formula,

`=COUNTIFS('C:\Users\User\Desktop\[Sales Data.xlsx]Automobile'!\$E\$5:\$E\$12, "Car")`

Here, 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. Now, if we don’t open the Sales Data workbook and press ENTER we will see the formula will show #VALUE! error. To solve this we have to open the workbook from where we are getting the data for the formula. After that we have to press F9 to refresh the formula. As a result, this time we will get the count. ## Conclusion

I hope now you know what to do when the COUNTIFS function is not working. If you have any confusion regarding any of the solutions please leave a comment.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Prantick Bala

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

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

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

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

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

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

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

• Reply 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″)

• Reply 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. Reply ITS VERY CLEAR STEP BY explanation . i also faced the same challenge as Adam. Thank for for the hints

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

• Reply Hello, OLUNIYI! Can you please email us your Excel file containing the dataset with the problem?

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

• Reply Naimul Hasan Arif Sep 25, 2022 at 3:25 PM

Thanks for sharing your valuable thoughts.

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

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

• Reply Hello Andrew, Your question is not clear to us. Can you send the Excel file to [email protected]? So, we can take a closer look at the issue. Advanced Excel Exercises with Solutions PDF  