**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"}))`

**Formula Breakdown**will count the number of`COUNTIF(D5:E13,{"Computer","Fridge"})`

**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

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

**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**