The COUNTIFS Function in Excel returns a value corresponding to the number of cells in the user-defined scope(s) or range(s) that match one or more defined criteria. It is very similar to the COUNTIF function, the key difference being that COUNTIFS can accommodate multiple pairings of range and criteria arguments.
The COUNTIFS Function has at least two required arguments (values separated by commas) and is written as follows:
=COUNTIFS(range1, criteria1, [range2, criteria2, ...])
The COUNTIFS function can accept up to 127 total pairs of range and criteria arguments.
As with most Excel functions, the COUNTIFS function only has certain inputs it can comprehend and calculate. Here's a quick sample of types of values that COUNTIF can process:
The COUNTIFS function uses Excel's logical operators evaluate matches. Here's a quick showcasing of their purposes:
| Operator | Meaning | |
|---|---|---|
| 1 | < | Less than |
| 2 | > | Greater than |
| 3 | <> | Greater than or less than (not equal) |
| 4 | <= | Less than or equal |
| 5 | >= | Greater than or equal |
The COUNTIF function also uses Excel's wildcard operators evaluate matches. Here's a quick showcasing of their purposes:
| Wildcard | Meaning | |
|---|---|---|
| 1 | ? | Single character wildcard |
| 2 | * | Wildcard string of undefined length |
As seen above, the wildcard operators in Excel are a question mark (?) and an asterisk (*). If you need to use a literal question mark or asterisk in their function, place a tilde (~) before the symbol (~?) (~*).
Take the following example of an Excel table with cars.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Model | Charger | Challenger | Durango | Journey | Caravan |
| 2 | Starting MSRP | $29,470 | $28,095 | $30,445 | $23,245 | $27,040 |
| 3 | Min. Horsepower | 305 | 305 | 305 | 206 | 206 |
| 4 | Max. Horsepower | 797 | 797 | 485 | 305 | 305 |
For instance, let's say one wanted to count the number of models above that have more than or equal to 305 horsepower, but cost less than 30000. One would use the following formula:
=COUNTIFS(B3:F3, ">=305", B2:F2, "<30000")The formula would result in a value of 2.
If one wanted to find the number of models with a lower Minimum Horsepower than the Charger that also cost less than the Caravan, one would use the following formula:
=COUNTIFS(B3:F3, "<"&B3, B2:F2, "<"&F2)The function would return a value of 1. When using a cell reference in the COUNTIFS function, one must CONCATENATE with the symbol "&".
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Address | Phone | |
| 2 | Beatrice Green | 4141 Hillcrest Ave | (360) 555 – 6565 | BGreen121@hotmail.com |
| 3 | William Wallace | 316 Parkside Drive | (360) 568 – 9888 | Wallace3145@outlook.com |
| 4 | Thomas Wallace | 317 Parkside Drive | (588) 716 – 3652 | Tomwall45@gmail.com |
The "?" wildcard in Excel is used in place of a single character.
The "*" wildcard in Excel is used in place of any string of characters. Unlike the "?" wildcard that only matches one character, the * wildcard matches any length of characters.
For example, if one wanted to find all the contacts whose name (of any length) ends in "Wallace" and whose address was exactly three digits beginning in 31 on Parkside Drive, they would use the following formula:
=COUNTIFS(A2:A4, "* Wallace", B2:B4, "31? Parkside Drive")The formula returns a value of 2.
The COUNTIFS function outlined in this tutorial is one of many belonging to the group of COUNT functions. There are many other COUNT functions used in Excel, suited for a variety of tasks.
If counting numbers only, use the COUNT function.
If counting numbers AND text, use the COUNTA function.
If counting empty cells, use the COUNTBLANK function.
If counting based on a single criterion, use the COUNTIF function.
If counting based on multiple criteria, use the COUNTIFS function.