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.
Syntax of the COUNTIFS Function
The COUNTIFS Function has at least two required arguments (values separated by commas) and is written as follows:
=COUNTIFS(range1, criteria1, [range2, criteria2, ...])
- Range1 (required) - The range argument defines which cells are being tested against the user’s criteria.
- Criteria1 (required) - The criteria argument is the user’s criteria definition that determines which cells are counted by the function.
- The range2 and criteria2 arguments, as well as all subsequent range and criteria pairs, are optional. They are written the same way as the range1 and criteria1 argument.
The COUNTIFS function can accept up to 127 total pairs of range and criteria arguments.
What Does the COUNTIFS Function Count in Excel?
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:
- Integers ( 1, 2, 3, 4, etc. )
- Decimals ( 1.2, 3.45, 6.789, etc. )
- Negative Values ( -1, -2, -3, etc. )
- Percentages ( 100%, 200%, 35%, etc. )
- Fractions ( 3/5, 5/3, 1 2/3, etc. )
- Empty Cells
- Text Values ( “Tree”, “The C4TS”, etc. )
- Logical Values ( TRUE & FALSE )
- Dates ( 3/17/2020, 'March 17, 2020', etc. )
- Time ( 2:52, 14:52, 00:14:52:55, etc. )
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 (~?) (~*).
Examples Using the COUNTIFS Function
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 "&".
Using the COUNTIFS Function with Wildcards
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 Excel Family of COUNT Functions
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.