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).
Condition range 1,
Condition 1, [
Condition range 2,
Condition 2], … )
|1||Range||The cell range of which the sum is calculated.|
|2||Condition 1 range||The range on which |
|3||Condition 1||The condition that’s applied to values in|
|4||Condition 2 range (Optional)||The range on which |
|5||Condition 2 (Optional)||The condition that’s applied to values in|
|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")
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:
|<>||NOT equal to|
|>=||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:
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.
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.
No, Countifs is not case sensitive. So a condition that checks for DUMBLEDORE will also find Dumbledore, dUMBLEDORE and dumbledore.
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.