How to Fix If the COUNTIF Function Is Returning 0 in Excel (4 Solutions)

 

Solution 1 – Insert an Inverted Comma While Using Text Value

In the following dataset, we haven’t inserted the text within the double quotation, so the formula has returned 0.

Insert an Inverted Comma While Using Text Value

Steps:

  • Select cell D15 and enter the following formula:
=COUNTIF(B5:E13,"Computer")

B5:E13 is the range, and “Computer” is the criteria for the COUNTIF function.

  • Press Enter. 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

Steps:

  • Enter the following formula in cell B16:
=COUNTIF(E5:E13,">"&E15)
  • Press Enter. You will get the total revenue earned over $60000, which is the return of the COUNTIF function. The return is 3.

Apply Actual Logical Operators While Using Values from Other Cells


Solution 3 – Using OR Logic to Avoid Returning 0

Steps:

  • Enter the following 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.
  • Press Enter. You have solved the problem.

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


Solution 4: – Using Wildcards If the COUNTIF Function Is Returning 0

Steps:

  • Enter the following formula in cell B16:
=COUNTIF(B5:E13,"*Oven*")
  • Press Enter. You will get the return of the COUNTIF function.

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: How to Use COUNTIF Function to Count Text from List in Excel


Download the Practice Workbook

Download this workbook to practice.


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

2 Comments
  1. SUPER HELPFUL, THANKS!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo