[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.


Download Practice Workbook

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


4 Suitable Solutions If COUNTIF Function Is Returning 0 in Excel

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.

countif returning 0


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.

Insert an Inverted Comma While Using Text Value

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

  • First of all, select cell D15, and type the corrected formula. The formula is,
=COUNTIF(B5:E13,"Computer")
  • Where B5:E13 is the range and “Computer” is the criteria of the COUNTIF function.
  • 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] Excel COUNT Function Not Working


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,

=COUNTIFS(F5:F12, "<E15 ")

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

Apply Actual Logical Operators While Using Values from Other Cells

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

  • Insert the below function in cell B16.
=COUNTIF(E5:E13,">"&E15)
  • 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.

Apply Actual Logical Operators While Using Values from Other Cells

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


Similar Readings


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,

=COUNTIFS(D5:E13,"Computer", D5:E13, "Fridge")

But the formula has returned 0. This is happening because the COUNTIFS function cannot calculate OR logic.

Using OR Logic to Avoid Returning 0

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.
=SUM(COUNTIF(D5:E13,{"Computer","Fridge"}))
Formula Breakdown
  • COUNTIF(D5:E13,{"Computer","Fridge"}) will count the number of Computers and then count the number of Fridges.
  • After that, the SUM function will sum up the total number of Computers and Fridges.
  • Further, press Enter on your keyboard. Afterward, you will be able to solve the problem.

Read More: [Solved!] Currency Sum Not Working in Excel (6 Suitable Solutions)


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-

=COUNTIF(B5:E13,"Oven")

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

Utilizing Wildcards If COUNTIF Function Is Returning 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.
=COUNTIF(B5:E13,"*Oven*")
  • After that, simply press Enter on your keyboard. Hence, you will get the return of the COUNTIF function. The return is

Notes

👉 #N/A! error arises when the formula or a function in the formula fails to find the referenced data.

👉 #DIV/0! error happens when a value is divided by zero(0) or the cell reference is blank.

Read More: [Solved]: COUNTBLANK Not Working in Excel


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

Tags:

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo