Course / Conditional Math

COUNTIFS, SUMIFS and MAXIFS

Filter by several conditions at once, every one of which must be true. Intermediate · 6 min · COUNTIFS, SUMIFS, MAXIFS, MINIFS

The functions ending in S take as many conditions as you like, written as range/criteria pairs. A row is included only if it meets all of them.

• =COUNTIFS(range1, criteria1, range2, criteria2, …)
• =SUMIFS(sum_range, range1, criteria1, …)
• =MAXIFS(max_range, range1, criteria1, …), plus MINIFS for the smallest

> SUMIFS, MAXIFS and MINIFS put the range to add or measure first. That's the opposite of SUMIF, where sum_range comes last.

Steps

1. How many e-bikes went out at weekends? In H2, count the rows where the bike in column B is E-bike and the day in column C is Weekend.

2. In H3, add up the fees in column E for City bikes rented for 3 hours or more.

3. What was the longest weekday rental of a Mountain bike? In H4, use MAXIFS to find the most hours in column D.

4. Now build a reusable finder. In H9, total the fees for the bike type in H7 and the day in H8. Point the criteria at those cells instead of typing the text.

Remember

• COUNTIFS, SUMIFS, MAXIFS and MINIFS take range/criteria pairs.
• A row is included only if it meets every condition.
• SUMIFS, MAXIFS and MINIFS put the value range first.
• Criteria can come from cells, which turns a formula into a reusable report.

Previous: SUMIF and AVERAGEIF · Next: SUMPRODUCT