Multi-Criteria: SUMIFS, COUNTIFS & AVERAGEIFS
The IFS family lets you total, count or average rows that match several conditions at once — the workhorses of real reporting.
What you will learn
- Add up rows matching two or more conditions with SUMIFS
- Count and average with multiple criteria
- Order the arguments correctly
When one condition is not enough
You met SUMIF and COUNTIF, which work on one condition — like total the North sales. But real questions usually stack conditions: total the North sales of Apples, or count the orders that were Shipped and over 100. For that you need the IFS family: SUMIFS, COUNTIFS and AVERAGEIFS. The extra S means plural conditions.
A worked example
Here is some order data. We want to answer questions that need two conditions at once:
| A (Region) | B (Product) | C (Sales) | |
|---|---|---|---|
| 2 | North | Apples | 120 |
| 3 | South | Apples | 80 |
| 4 | North | Bananas | 90 |
| 5 | North | Apples | 100 |
| 6 | South | Bananas | 60 |
SUMIFS: total when several things are true
SUMIFS adds up numbers only from rows that match all the conditions you give. Its order is different from SUMIF — the range to add comes first, then each pair of (range to test, value to match):
=SUMIFS(C2:C6, A2:A6, "North", B2:B6, "Apples")Note: Output: 220 SUMIFS looked for rows where column A is North AND column B is Apples. Two rows qualified (120 and 100), so it added them to 220. The South-Apples row (80) was skipped because its region did not match, and North-Bananas (90) was skipped because its product did not match.
Watch out: Mind the argument order. SUMIF puts the sum range LAST: SUMIF(test, value, sumrange). SUMIFS puts the sum range FIRST: SUMIFS(sumrange, test1, value1, test2, value2, ...). Mixing them up is the most common SUMIFS mistake.
COUNTIFS and AVERAGEIFS work the same way
These two follow the same (range, condition) pairs. COUNTIFS just counts matching rows (no sum range), and AVERAGEIFS averages the matching numbers (sum range first, like SUMIFS):
=COUNTIFS(A2:A6, "North", B2:B6, "Apples")
=AVERAGEIFS(C2:C6, A2:A6, "North", B2:B6, "Apples")Note: Output: 2 (two rows are North AND Apples) 110 (their average sales: (120 + 100) / 2) Both functions used the same two conditions. COUNTIFS reported how many rows matched (2). AVERAGEIFS averaged the sales of those same two rows to 110.
Conditions can be comparisons too
A condition is not limited to exact text. Put a comparison in quotes to filter on size. To total sales over 100 in the North only:
=SUMIFS(C2:C6, A2:A6, "North", C2:C6, ">100")Note: Output:
120
The North rows are 120, 90 and 100. Of those, only 120 is greater than 100, so the total is 120. The comparison ">100" goes in quotes, just like in COUNTIF.
Tip: You can use a cell instead of a typed value: SUMIFS(C2:C6, A2:A6, E1) totals sales for whatever region you type into E1. Point your conditions at input cells and your report becomes interactive — change one cell and every total updates.
Q. Which formula correctly totals the Sales in C2:C6 where Region (A) is South AND Product (B) is Bananas?
✍️ Practice
- Recreate the five-row order table and use SUMIFS to total the North-Apples sales.
- Use COUNTIFS to count how many orders were South AND over 50, then AVERAGEIFS for their average.
🏠 Homework
- Build a 10-row table with Region, Product and Sales. Write three formulas: total sales for one region-and-product pair (SUMIFS), the count of those rows (COUNTIFS), and their average (AVERAGEIFS).