The Excel COUNTIFS function counts values (numbers, dates, text) in a given cell range if they meet multiple criteria. For example, you could use it to count the number of people that drive a certain car (1st condition) and whose income is larger than a certain amount (2nd condition).

## Syntax

**=COUNTIFS(**`Range`

**,** `Condition range 1`

**, **`Condition 1`

**, [**`Condition range 2`

**, **`Condition 2`

**], … ) **

Argument | Argument description | |
---|---|---|

1 | Range | The cell range of which the sum is calculated. |

2 | Condition 1 range | The range on which is applied. If the condition is met for a value in this range, the corresponding value in is counted. |

3 | Condition 1 | The condition that’s applied to values in . If the condition is met, corresponding values in are counted. |

4 | Condition 2 range (Optional) | The range on which is applied. If the condition is met for a value in this range and the condition(s) in the other condition range(s) are met as well, the corresponding value in is counted. |

5 | Condition 2 (Optional) | The condition that’s applied to values in . If the condition is met for a value in this range and the condition(s) in the other condition range(s) are met as well, the corresponding value in is counted. |

6, 7, … | Condition range 3, Condition 3, … (Optional) | Up to 127 pairs of condition ranges and conditions. |

## Example 1: A basic Excel Countifs formula

Let’s start with a simple way of using Countifs. A basic formula could look something like this:

`=COUNTIFS(B2:B6,"<4", C2:C6, "Yes")`

### Explanation

Excel returns the value 2. That’s because the formula pairwise checks the values in the cell ranges **B2:B6** and **C2:C6**. For values in **B2:B6** it checks if the value is smaller than 4. And for the values in **C2:C6** it checks if the value is *Yes*. Only if both conditions are met, the counter goes up by 1.

So let’s walk through it for ourselves: starting with **B2 **and **C2**. **B2 **contains *1 *and **C2 **contains *Yes*, so both conditions are satisfied and we count this pair of values: 1.

In the same way, **B3 **and **C3 **contain *2 *and *Yes*, so add 1 to the count. Then, **B4 **contains a *3*, which is smaller than 4. But **C4 **contains *No*. So not all conditions are met and we do not count this pair.

Similarly, **B5 **and **C6 **are not counted because the value *4* (in cell **B5**) **is not smaller than** *4*. And **B6 **and **C6 **are not counted because **C6 **contains *No*.

The result ends up being 2, for the pairs **B2:C2 **and **B3:C3 **that satisfied the conditions.

## Example 2: More condition types

In the previous example, we created conditions that check for less than (<) and for things equal to the word *Yes*. But there are more types of conditions we can use. Those will allow us to create more interesting conditions.

For example, an often-used way of creating conditions is by combining any sort of data (numbers, text, dates, etc) with a *comparison operator*. Here’s an overview of the operators you can use to form conditions:

Operator | Meaning |
---|---|

= | Equal to |

<> | NOT equal to |

> | Greater than |

< | Less than |

>= | Greater than or equal to |

<= | Less than or equal to |

When you use these comparison operators, make sure you surround them in quotes “. That’s a requirement for Excel to understand what you mean.

Let’s use these comparison operators in an example:

### Explanation

The Countifs function returns the value 2. That’s because we’re looking in cell range **C2**:**C4** for cells that contain values larger than or equal to 30. While at the same time, we’re also looking in cell range **D2**:**D6** for values **not equal to** *Retired*. If both of these conditions are fulfilled, we count this row.

There are two value pairs that meet the conditions: **C3:D3** and **C5:D5**. That’s why a 2 is returned by Countifs.

## Note: Using wildcards with Countifs

You can use many sorts of conditions as `Condition`

arguments. Conditions can be texts, numbers, and dates. And they can be combined with conditional operators.

Another useful feature that you can use in the `Condition`

argument are wildcards: characters like *?* and *** that allow your textual conditions to match with an approximate match instead of an exact word match.

**Note:** for more information about wildcards, you can check out this section about wildcards from our Find and Replace post.

## Questions

**Can an array/multiple arrays be used instead of cell ranges?**

No, the Countifs function (and this goes for Countif as well) is not compatible with arrays as input arguments. But you *can *use the Sumproduct function as a workaround.

**Is Countifs case sensitive?**

No, Countifs is **not **case sensitive. So a condition that checks for *DUMBLEDORE *will also find Dumbledore, dUMBLEDORE and dumbledore.

## Drawbacks

The Excel Countifs function has some drawbacks:

- We’ve mentioned it before. You cannot use arrays instead of cell ranges with the COUNTIFS function. Actually, arrays cannot be used with any of the IF(S) family of functions: SUMIF(S), COUNTIF(S), AVERAGEIF(S) all cannot use arrays. However, you
**can**use the SUMPRODUCT function with arrays. Often, you can restructure your formula to use Sumproduct instead of one of the other functions of the IF(S) family of Excel functions.