Md. Abdur Rahim Rasel
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,
=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] 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,
=COUNTIFS(F5:F12, "<E15 ")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.
=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.
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,
=COUNTIFS(D5:E13,"Computer", D5:E13, "Fridge")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.
=SUM(COUNTIF(D5:E13,{"Computer","Fridge"}))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.
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.
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
👉 #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.
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
- How to Use COUNTIF for Non Contiguous Range in Excel
- Excel COUNTIF to Count Cell That Contains Text from Another Cell
- How to Use COUNTIF Function to Count Text from List in Excel
- Count Text at Start with COUNTIF & LEFT Functions in Excel
- How to Compare Two Columns Using COUNTIF Function
- How to Use Excel COUNTIF Between Time Range
- How to Use COUNTIF to Count Date Less Than Today in Excel
- Excel COUNTIF to Count Cells Greater Than 1
- COUNTIF Function to Count Cells That Are Not Equal to Zero
- How to Use COUNTIF Function In Excel to Count Bold Cells
- How to Use COUNTIF Function to Calculate Percentage in Excel
- How to Use COUNTIF Function in Excel Greater Than Percentage
- How to Use COUNTIF Function with Array Criteria in Excel
- How to Use SUMIF, COUNTIF and AVERAGEIF Functions in Excel
- How to Use the Combination of COUNTIF and SUMIF in Excel
- Difference Between SUMIF and COUNTIF Functions in Excel
- How to Use IF and COUNTIF Functions Together in Excel
- How to Use Nested COUNTIF Function in Excel
- How to Use COUNTIF and COUNTA Functions Together in Excel
- How to Calculate Frequency Using COUNTIF Function in Excel
- Excel COUNTIF Function with Conditional Formatting
