[Fixed] COUNTIFS Not Working in Excel (6 Possible Solutions)

In Excel, the COUNTIFS function sometimes stops working correctly and returns 0 or even errors. For these reasons, you can not get an accurate count result based on certain conditions. After existing all the conditions in the dataset, the COUNTIFS formula can not understand them. You can get errors in your project tracking, sales analysis, and reports analysis tasks because of this issue. Mismatched range sizes, missing double quotes in the text condition, improper use of logical operators, and incorrect criteria format are the most frequent causes of this issue.

In this article, we will find the reasons why COUNTIFS is not working and provide step-by-step instructions for resolving each issue.

In the following dataset, we have a simple product details dataset. Column A lists the Product ID, Column B contains the Product Category, Column C shows the Region of the product, Column D holds the Quantity of the Product, and Column E includes the Order Date.

When you are using the COUNTIFS function, the text criteria must be in double quotes with text conditions. If you don’t use double quotes, Excel will interpret the condition as a name, number, or formula, and COUNTIFS will return 0. Here we are trying to count Apple in the product column without using any double quotation marks, which is why the COUNTIFS function is returning 0.

To solve this error, you need to keep in mind that text criteria should be written in double quotes, and numbers can be written without them.

Steps to fix this issue:

➤ Select cell B14 and write this formula

➤ Press Enter, and see the count result is 5.

The COUNTIFS function will not work when the ranges used in the formula are not the same size. All the criteria ranges need to have the same number of rows and columns. If one range is longer or shorter than the others, Excel will return a #VALUE! Error.

To solve this issue, you need to make sure that every range size is the same; Then you can get the correct result.

Steps to fix this issue:

➤ Select cell B14 and write this formula

➤ Press Enter, and see the count result is 4.

The COUNTIFS function works with AND logic; all conditions must be met at the same time. When you want to apply multiple conditions at the same time with OR logic, Excel may return 0 or an incorrect result.

To solve this, you need to apply multiple COUNTIF functions with a plus sign (+) or use an array constant with SUM(COUNTIFS(…)). The OR logic will be applied properly, and you can get an accurate count result.

Steps to fix this issue:

➤ Select cell B13 and write this formula

➤ Press Enter, and see the count result is 8.

The criteria_range in COUNTIFS only accepts a plain range of cells, not a formula-generated array. When you try to insert the LEFT, RIGHT, or TRIM functions with COUNTIFS, Excel will return an error or 0 because it cannot calculate the criteria properly.

To fix this, you need to apply the COUNTIFS function with an actual range reference, not a function-generated array.

Steps to fix this issue:

➤ Select cell B13 and write this formula

➤ Press Enter, and see the count result is 5.

If you forget to place double quotes in the text criteria or apply numeric/logical operators incorrectly, the function will return 0 or show an error. Because Excel will not understand >10 as a valid criterion.

To solve this problem, you need to use double quotes (like “>10”) to ensure COUNTIFS can correctly understand and count the values.

Steps to fix this issue:

➤ Select cell B13 and write this formula

➤ Press Enter, and see the count result is 2.

If you want to count only part of a text string in your dataset, COUNTIFS will not understand and return 0 because it only looks for an exact match. If you don’t use a wildcard, COUNTIFS will not work, and you cannot get the expected result.

To fix this, you need to use * (asterisk) wildcards—  for any sequence of characters and ? (question mark) for a single character. After that, COUNTIFS will count all cells correctly, no matter what text appears before or after it.

Steps to fix this issue:

➤ Select cell B13 and write this formula

➤ Press Enter, and see the count result is 2 because there are two oranges in column B.

Frequently Asked Questions

Is COUNTIFS case-sensitive?

No, COUNTIFS is not case-sensitive. For example, “Apple” and “apple” will be treated as the same.

What are the reasons that the COUNTIFS function returns a #VALUE! Error?

COUNTIFS function returns the #VALUE! Error if the criteria_range sizes don’t match. For example, if one range has 10 rows and another has 12, Excel will return the error #VALUE!

Can COUNTIFS count blank cells?

Yes. To count blank cells, write this formula:

=COUNTIFS(B2:B11,””) 

Wrapping Up

There are a lot of reasons why COUNTIFS does not work in Excel: text criteria are not in quotes, improperly formatted criteria, or range sizes are mismatched. COUNTIFS is unable to count the actual value due to these problems.

In this article, we’ve explained step-by-step how to fix each problem— ensuring criteria are in quotes, using wildcards properly, and applying OR logic correctly.

Hopefully, you have enjoyed the article. Feel free to download the practice file and share your thoughts and suggestions in the comment box.