Excel Exercises - COUNTIFS Function (2024)

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

Excel Exercises - COUNTIFS Function (2024)

References

Top Articles
Latest Posts
Recommended Articles
Article information

Author: Delena Feil

Last Updated:

Views: 5543

Rating: 4.4 / 5 (45 voted)

Reviews: 92% of readers found this page helpful

Author information

Name: Delena Feil

Birthday: 1998-08-29

Address: 747 Lubowitz Run, Sidmouth, HI 90646-5543

Phone: +99513241752844

Job: Design Supervisor

Hobby: Digital arts, Lacemaking, Air sports, Running, Scouting, Shooting, Puzzles

Introduction: My name is Delena Feil, I am a clean, splendid, calm, fancy, jolly, bright, faithful person who loves writing and wants to share my knowledge and understanding with you.