[Solved!]: Excel COUNTIF Returning 0 Instead of Actual Value

Excel COUNTIF function counts values that match criteria from a range. You may sometimes face the issue that the function is returning 0. Today, in this article, I’ll introduce you to four solutions that can be taken when the COUNTIF function is returning 0 in Excel effectively with appropriate illustrations.

How to Fix If COUNTIF Function Is Returning 0 in Excel: 4 Suitable Solutions

Consider the following dataset. Let’s use this dataset to demonstrate what actions to take when the COUNTIF function is returning 0. Here’s an overview of the dataset for our today’s task.

Solution 1: Insert an Inverted Comma While Using Text Value

When we count text strings the text string must be inserted inside of an inverted comma or double quotation mark (“ ”). Otherwise, the COUNTIF 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 the double quotation. So the formula has returned 0.

Let’s follow the instructions below to fix the problem!

• First of all, select cell D15, and type the corrected formula. The formula is,

• Hence, simply press Enter on your keyboard. As a result, you will get the total number of sold Computers which is the return of the COUNTIF function. The return is 3.

Read More: [Fixed] COUNTIF Function with Wildcard Not Working in Excel

Solution 2: Apply Actual Logical Operators While Using Values from Other Cells

When we use a cell reference as the criteria of the COUNTIF 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 cell E15 as the criteria in the COUNTIF function. So we have typed the following formula,

Here we have directly inserted the cell reference in the formula.

Let’s follow the instructions below to fix the problem!

• Insert the below function in cell B16.

• After that, simply press Enter on your keyboard. As a result, you will get the total number of revenue earned more than $60000 which is the return of the COUNTIF function. The return is 3.

Read More: [Fixed!] Excel COUNTIF Function Not Working for String “True”

Solution 3: Using OR Logic to Avoid Returning 0

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 COUNTIF function won’t work properly. Suppose we want to get the number of sellers of Computer or Fridge. So, we have typed the formula,

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 COUNTIF function together to calculate OR logic. Let’s follow the instructions below to fix the problem!

• Write down the below formula in cell E15.

• Further, press Enter on your keyboard. Afterward, you will be able to solve the problem.

Read More: Excel COUNTIF to Count Cell That Contains Text from Another Cell

Solution 4: Utilizing Wildcards If COUNTIF Function Is Returning 0

We can use different Wildcards in different conditions when COUNTIF is returning 0. For Example, if we want to match a partial string from a text string we can use an asterisk (*). Suppose we have inserted Oven as the criteria in our formula-

Now as we have Microwave Oven in our dataset, the COUNTIFS function won’t work properly and will return 0.

As the criteria are now between the asterisks (*), the function will look for partial matches in the range B5:E13. Let’s follow the instructions below to fix the problem!

• Insert the below function in cell B16.

• After that, simply press Enter on your keyboard. Hence, you will get the return of the COUNTIF function. The return is

Read More: How to Use COUNTIF Function to Count Text from List in Excel

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

I hope all the suitable solutions mentioned above to fix the returning 0 in the COUNTIF function will provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

Related Articles

• Count Text at Start with COUNTIF & LEFT Functions in Excel
• How to Use COUNTIF Function In Excel to Count Bold Cells

<< Go Back to Excel COUNTIF Function | Excel Functions | Learn Excel