**COUNTIF** vs **COUNTIFS** is a key phrase to differentiate between two functions; the **COUNTIF** and **COUNTIFS**. The **COUNTIF** function is a **Statistical function** that counts cells based on a criterion. On the other hand, the **COUNTIFS** function imposes multiple criteria to multiple ranges then counts those cells that match all the criteria in respective ranges. In this article, we demonstrate core differences between the **COUNTIF** and **COUNTIFS** functions with examples.

In the above screenshot, we display an overview of the **COUNTIF** and **COUNTIFS** function’s operability and outcomes.

**Table of Contents**hide

**Download Excel Workbook**

**Excel COUNTIF vs COUNTIFS Functions: Syntax and Arguments**

**🔄 Function Objective: **

**COUNTIF**; counts cells that meet a criterion within a range.

**COUNTIFS**; counts cells that meet multiple criteria within multiple ranges.

**🔄 Syntax:**

`COUNTIF (range, criteria)`

`COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)`

** ****🔄 Arguments Explanation:**

Argument | Required/Optional | Explanation |
---|---|---|

range/criteria_range |
Required |
only or first range where respective criteria is applied |

criteria/criteria1 |
Required |
the only or first criteria we provide to count or match cells within a range or criteria_range |

criteria_range2, criteria2 |
Optional |
additional range and their respective criteria up to 127 of them are applicable. |

**🔄 Return Parameter:**

A number of existing cells that meet the single criterion or criteria.

**🔄 Applies To:**

Microsoft Excel version **2007**, Excel MAC version **2011**, and onwards.

**Differences between COUNTIF and COUNTIFS in Excel**

**1. Basics of COUNTIF and COUNTIFS Functions**

As we know from the syntax, the **COUNTIF** function takes only one range and a single criterion. On the contrary, the **COUNTIFS** function takes multiple criteria to be imposed in multiple ranges. In this case, we indicate the basic differences in these two functions according to their syntax.

**COUNTIF Function**

The** COUNTIF** function operates with a single range and criterion.

⏩ We apply the below formula to come up with a number of cells that satisfy a given criterion.

`=COUNTIF(C8:C19,C8)`

In the formula,

**C8:C19; **is the range.

**C8; **direct the criterion**.**

⏩ Press **ENTER**, the formula counts all the cells containing text **“Boston**” (i.e., **C8**).

From the dataset, we can see there are** 4** matches, and the formula returns **4** maintaining just one criterion i.e. **C8**.

If we are interested in adding another criterion in the **COUNTIF** function Excel won’t allow us, in that case, we have to use the **COUNTIFS** function instead.

**COUNTIFS Function**

Similar to the **COUNTIF** function the **COUNTIFS** counts cells, but it allows multiple criteria to be imposed in multiple ranges. Let’s extend the previous criteria we use for **COUNTIF**. We add another two criteria to fetch the number of cells that match all the criteria imposed.

⏩ Paste the following formula in any cell (i.e., **D5**).

`=COUNTIFS(C8:C19,C9,D8:D19,D8,E8:E19,E12)`

Inside the formula, we impose three criteria (i.e., **C9, D8, **&** E12**) in three different ranges (i.e., **C8:C19,D8:D19, **&** E8:E19**).

⏩ Hit **ENTER**, the number of cells matching all three criteria appears.

From the dataset, we see only one entry matches all the imposed criteria as the formula returns. We can apply up to **127** criteria, however, for better understanding and representation we are applying two or three conditions at a time.

In the end, the following image depicts the basic difference between these two functions.

**Read More: ****VBA COUNTIF Function in Excel (6 Examples)**

**2. Handling Multiple Criteria with COUNTIF and COUNTIFS**

The **COUNTIF** function can’t handle multiple criteria whereas the **COUNTIFS** function does it naturally. We can use multiple **COUNTIF** functions in addition to inserting multiple criteria, but it returns all the cells’ numbers for each criterion then adds them. On the contrary, we simply insert multiple criteria to be imposed in multiple ranges.

**With COUNTIF Function**

To insert multiple criteria, we use multiple **COUNTIF** functions then assign a criterion in each with a different range,

⏩ Type the following formula in any adjacent cell (i.e., **D4**).

`=COUNTIF(C8:C19,C8)+COUNTIF(D8:D19,D9)+COUNTIF(E8:E19,E10)`

Inside the formula, we use three **COUNTIF** functions to assign three ranges and criteria.

⏩ After Pressing **ENTER**, the number of matched cells appears similar to the below image.

If we inspect the dataset, we see the **COUNTIF** formula adds all the cells that match criteria to respective ranges, not the number of cells that satisfy all the criteria. And it’s the main drawback of the **COUNTIF** function.

**With COUNTIFS Function**

Now, if we want to count the number of cells that satisfy the same three criteria using the **COUNTIFS** function, it returns a different number, and the dataset backs it.

⏩ Write the following formula in any cell (i.e., **D5**).

`=COUNTIFS(C8:C19,C8,D8:D19,D9,E8:E19,E10)`

The formula declares criteria (i.e., **C8,D9,& E10**) to match in ranges (i.e., **C8:C19,D8:D19, & E8:E19**) respectively.

⏩ Afterwards you Hit **ENTER**, the number of matched cells appears similar to the below picture.

We can ensure the result using the dataset that only one entry matches all three criteria.

In comparison, we can see the following image as core dissimilarity handling multiple criteria by **COUNTIF** vs **COUNTIFS** functions.

**Read More:** **How to Use Excel COUNTIF That Does Not Contain Multiple Criteria**

**Similar Readings**

**COUNTIF Excel Example (22 Examples)****How to Use COUNTIF Between Two Numbers (4 Methods)****Excel COUNTIF to Count Cell That Contains Text from Another Cell****Apply COUNTIF Function in Multiple Ranges for Same Criteria****How to Apply COUNTIF Between Two Cell Values in Excel**

**3. Counting Cells with COUNTIF and COUNTIFS**

In Excel, we often count non-blank text cells. In that case, **COUNTIF** and **COUNTIFS** both perform well. The issue arises when we insert a particular text string to be counted in the non-blank text cells.

**With COUNTIF Function**

**COUNTIF** only counts the non-blank text cells, any imposed condition or certain text can’t be performed.

⏩ Type the following formula in any cell (i.e., **D4**).

`=COUNTIF(B8:E19,"*")`

** ****“*” **enables the formula to count non-blank text cells from the dataset.

⏩ As you hit **ENTER**, you see the number of non-blank text cells as shown in the image below.

There are **36** text cells existing in the dataset and the formula returns it.

**With COUNTIFS Function**

The **COUNTIFS** function can count the same text non-blank cells as the **COUNTIF** function does but it offers an option to match the certain text within the text cells.

⏩ Write the below formula in any cell (i.e., **D5**).

`=COUNTIFS(B8:E19,"*",B8:E19,"*rk")`

** ****“*” **enable to count all the text cells within the range and “*rk” match with text cells that have **rk** at their end.

⏩ After Pressing **ENTER**, you see only **4** entries as such that have **rk** at their end as shown in the below picture.

If we compare these two operations, we see commonness in their working process but multi-dimensions in just the **COUNTIFS** function’s result.

**Read More:** **Count Blank Cells with Excel COUNTIF Function: 2 Examples**

**4. Finding Match with COUNTIF and COUNTIFS**

Similar to counting cells, we can count cells based on a specific text; partial or full. **With wildcards**, we can search for a partial match text in the dataset.

**With COUNTIF Function**

The **COUNTIF** function matches a partial text while we enter the text with wildcards (i.e., *****) in the formula. We can use other wildcards such as an asterisk (*****), question mark (**?**), and tilde (**~**).

⏩ Write the following formula in any cell (i.e., **D4**).

`=COUNTIF(B8:E19,"*Chip")`

The formula matches texts that have **Chip** at the end. And the **“*Chip” **works as a criterion.

⏩ To bring out the result Hit **ENTER**, the matching number of cells that obey the criterion appears like the below picture.

**With COUNTIFS Function**

The **COUNTIFS** matches the text as **COUNTIF** does but it takes additional texts to satisfy criteria. By combining them we have a match that tends to be very useful searching entries in huge datasets.

⏩ Paste the following formula in any cell (i.e., **D5**).

`=COUNTIFS(D8:D19,"*ers",E8:E19,"*eat")`

The formula matches both **“*ers” **and **“*eat”** in the end within the ranges.

⏩ Press **ENTER**, the matched number of the cells appears.

Just looking at the dataset, you can tell only one entry matches imposed criteria.

The below picture offers an overview of what we want to differentiate between the functions. Though it seems it’s an example of imposing multiple criteria, we can still use it to find the matches from any dataset.

**Read More: ****Count Text at Start with COUNTIF & LEFT Functions in Excel**

**Conclusion**

I hope the above-described examples of the **COUNTIF vs COUNTIFS** functions offer you a basic differentiation in their usage of them. Also, I think this article provides a clear concept about where to use the **COUNTIF** and **COUNTIFS** functions. If you have further queries or feedback, please let me know in the comment section.