COUNTIFS
The COUNTIFS function returns the number of cells in one or more collections that satisfy given conditions (one condition per collection).
COUNTIFS(test-values, condition, test-values…, condition…)
test-values: A collection containing values to be tested. test-values can contain any value.
condition: An expression that compares or tests values and results in the boolean value TRUE or FALSE. condition can include comparison operators, constants, the ampersand concatenation operator, references and wildcards. You can use wildcards to match any single character or multiple characters in the expression. You can use a ? (question mark) to represent one character, an * (asterisk) to represent multiple characters, and a ~ (tilde) to specify that the following character should be matched rather than used as a wildcard. condition can also contain a REGEX function instead of wildcards.
test-values…: Optionally include one or more additional collections containing values to be tested. Each test-values collection must be followed immediately by a condition expression. This pattern of test-values, condition can be repeated as many times as needed.
condition…: If an optional collection of test-values is included, condition… is an additional expression that results in a boolean TRUE or FALSE. There must be one condition expression following each test-values collection; therefore, this function always has an even number of arguments.
Notes
Each value in test-values is compared to the corresponding condition. If the corresponding values in each collection meet the corresponding conditional tests, the count is increased by 1.
Examples |
|---|
Given the following table: |
A | B | C | D | |
|---|---|---|---|---|
1 | Age | Sex | Status | Salary |
2 | 35 | M | M | 71,000 |
3 | 27 | F | M | 81,000 |
4 | 42 | M | M | 86,000 |
5 | 51 | M | S | 66,000 |
6 | 28 | M | S | 52,000 |
7 | 49 | F | S | 62,000 |
8 | 63 | F | M | 89,000 |
9 | 22 | M | M | 34,000 |
10 | 29 | F | S | 42,000 |
11 | 35 | F | M | 56,000 |
12 | 33 | M | S | 62,000 |
13 | 61 | M | M | 91,000 |
=COUNTIFS(A2:A13, "<40", B2:B13, "=M") returns 4, the number of males (indicated by an "M" in column B) under the age of 40. =COUNTIFS(A2:A13, "<>35", B2:B13, "=M") returns 6, the number of males (indicated by an "M" in column B) who are not 35 years old. You can also use the ≠ operator. =COUNTIFS(A2:A13, "<40", B2:B13, "=M", C2:C13, "=S") returns 2, the number of males who are single (indicated by an "S" in column C) and under the age of 40. =COUNTIFS(A2:A13, "<40", B2:B13, "=M", C2:C13, "=M") returns 2, the number of males who are married (indicated by an "M" in column C) and under the age of 40. =COUNTIFS(A2:A13, "<40", B2:B13, "=F") returns 3, the number of females (indicated by an "F" in column B) who are under the age of 40. |
Example using REGEX |
|---|
Given the following table: |
A | B | |
|---|---|---|
1 | 45 | |
2 | 41 | Aaron |
3 | 29 | |
4 | 64 | |
5 | 12 | Sarah |
=COUNTIFS(B1:B5, REGEX("([A-Z0-9a-z._% -] )@([A-Za-z0-9.-] \.[A-Za-z]{2,4})"), A1:A5, ">10") returns the number of cells in A1:A5 that are larger than 10 and where the corresponding cell in B1:B5 contains an email address. |