ExcelInsider Team
The COUNTIF function can sometimes unexpectedly return 0 in Excel. When you are doing data analysis in Excel, you can’t get the actual count result because of this problem. You can find that the COUNTIF function criteria exist in your dataset, but the result is showing 0. Because of this problem, you can’t get correct reports, track inventory, analyze sales, or manage any dataset. Missing quotes for text criterion, unnecessary spaces, numbers stored as text, or applying the wrong formula for numerous conditions are the causes for these errors.
In this article, we will find the cause of why the COUNTIF returns 0 and will discuss in detail how you can solve these problems.
Here is how you can fix the COUNTIF returning 0 issue in Excel:
➤ Text criteria must be in double quotes like this: =COUNTIF(A2:A11, “Apple”)
➤ Use the asterisk (*) as a wildcard in the formula: =COUNTIF(A2:A11, “*App*”)
➤ Using AND logic, write this formula: =COUNTIFS(A2:A11, “Apple”, C2:C11, “>0”)
➤ Using OR logic, select cell B13 and write this formula: =SUM(COUNTIF(A2:A11, {“Apple”,”Mango”}))
➤ Press Enter and see that the COUNTIF is returning 4
Ensure Text Criteria Are in Quotes
In the following dataset, we have a simple product category details dataset. Column A lists the Product Names, Column B contains the Product Category, Column C shows the Quantity of the product, Column D holds the Price List, and Column E includes the Order Date.
We’ll use this dataset in Excel to demonstrate the common issues that can cause COUNTIF to return 0 in Excel. This will help ensure that COUNTIF is returning the correct count result.
When you are using the COUNTIF 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 COUNTIF will return 0. Here, we are trying to count Apple in the product column without using any double quotation marks, which is why the COUNTIF 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 B13 and write this formula
=COUNTIF(A2:A11, “Apple”)
➤ Press Enter, and see the count result is 3.
Use Wildcards for Partial Matches
We will use the asterisk (*) as a wildcard in this method to solve the COUNTIF returning 0 issue because it can match any string of characters. If we want to look for an exact match and the COUNTIF function is returning 0 because of too much text or word variations, we must use wildcards to partially match.
In our dataset, we are trying to count Apple without using the asterisk sign. The COUNTIF function cannot match this word; that’s why it is returning 0.
Steps to fix this issue:
➤ Select cell B13 and write this formula
=COUNTIF(A2:A11, “*App*”)
➤ Press Enter and see that the COUNTIF is returning 3
Using OR logic: SUM of COUNTIFs
The COUNTIF function cannot handle multiple criteria, and it will return 0.
In this method, we will use the OR logic with COUNTIF to solve the COUNTIF returning 0 problem. Inside the COUNTIF function is an array that will be constant. Each condition will be checked by Excel, and the SUM function will add them.
In our dataset, we are trying to count Apple and Mango, and the result is showing 0 because the COUNTIF function cannot work with multiple conditions.
Steps to fix this issue:
➤ Select cell B13 and write this formula
=SUM(COUNTIF(A2:A11, {"Apple","Mango"}))
➤ Press Enter and see that the COUNTIF is returning 4
Using COUNTIFS (AND logic)
The COUNTIF function will return 0 if you want to search product names in the Qty column, and the COUNTIF function cannot work with multiple conditions.
In our dataset, “Apple” is searching in the quantity column with the COUNTIF function, which is why it is returning 0.
To solve the COUNTIF returning 0 problem, we need to use the COUNTIFS function because it can handle multiple criteria with AND logic and gives us a proper result.
Steps to fix this issue:
➤ Select cell B13 and write this formula
=COUNTIFS(A2:A11, "Apple", C2:C11, ">0")
➤ Press Enter and see that the COUNTIF is returning 2
Frequently Asked Questions
What are the reasons that the COUNTIF function is not working with cell references?
At first, double-check the formatting and data type. If the lookup cell has hidden space, the cell formatting is not correct, and the data type is not correct, then the COUNTIF function will return 0 when using a cell reference.
Can the COUNTIF function check multiple conditions?
No, the COUNTIF function works with only one condition.
What are the reasons to ignore error cells by the COUNTIF function?
The COUNTIF function ignores error cells because it cannot count them.
Can I apply the COUNTIF function on multiple sheets?
Yes. Use this formula to apply the COUNTIF function on multiple sheets
=COUNTIF(Sheet2!A2:A20, “Apple”).
Make sure that the sheet name is written correctly and that there are no gaps between the quotes.
Wrapping Up
There are a lot of reasons why COUNTIF can return 0: mismatched text and numbers, improperly formatted criteria, or improperly used formulas. COUNTIF 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/AND 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.
