Excel Countifs Function - Syntax and examples

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 `Condition 1` is applied. If the condition is met for a value in this range, the corresponding value in `Range` is counted.
3	Condition 1	The condition that’s applied to values in`Condition range 1` . If the condition is met, corresponding values in `Range` are counted.
4	Condition 2 range (Optional)	The range on which `Condition 2` 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 `Range` is counted.
5	Condition 2 (Optional)	The condition that’s applied to values in`Condition range 2` . 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 `Range` 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")

A basic COUNTIFS example. Takes a cell range with values 1 through 5 and counts them if their value is smaller than 4 and the other range contains Yes in the corresponding cell. In this case, the Countifs function returns 2. — A basic COUNTIFS example. Takes a cell range with values 1 through 5 and counts them if their value is smaller than 4 and the other range contains *Yes* in the corresponding cell. In this case, the Countifs function returns 2.

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:

Counting pairs of values where age is greater than or equal to 30 and Status is **not** *Retired*.

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