SUMIF and AVERAGEIF: practice exercises
SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS. 5 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.
In H2, total the training hours for the North region.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Employee | Team | Region | Attendance | Training hours | |||
| 2 | Grace | Sales | North | 97% | 64 | North region hours | ||
| 3 | Tom | Finance | South | 88% | 22 | Support average attendance | 91.4% | |
| 4 | Priya | Sales | East | 99% | 81 | Sales South region hours | 46 | |
| 5 | Marek | Support | North | 91% | 35 | Finance average hours (90%+ attendance) | 58.667 | |
| 6 | Nia | Finance | East | 95% | 58 | |||
| 7 | Ollie | Support | South | 84% | 17 | |||
| 8 | Lucy | Sales | South | 93% | 46 | |||
| 9 | Hamza | Finance | North | 89% | 29 | |||
| 10 | Rachel | Support | East | 98% | 73 | |||
| 11 | Sunil | Sales | North | 86% | 31 | |||
| 12 | Tanya | Finance | South | 96% | 52 | |||
| 13 | Will | Support | North | 94% | 40 | |||
| 14 | Zoe | Sales | East | 92% | 49 | |||
| 15 | Ben | Finance | North | 97% | 66 | |||
| 16 | Ella | Support | South | 90% | 38 |
How it works
SUMIF(range to check, criteria, range to add) adds up only the rows that match. AVERAGEIF works the same way.
The S versions take more than one condition, and put the range to add first: SUMIFS(range to add, range1, criteria1, range2, criteria2…).
=SUMIF(B2:B6,"Sales",D2:D6) =AVERAGEIF(B2:B6,"Sales",D2:D6) =SUMIFS(D2:D6,B2:B6,"Sales",C2:C6,"North") =SUMIF($B$2:$B$6,F5,$D$2:$D$6)
What SUMIF does
SUMIF adds up only the rows that match: the hours worked by Sales, the money spent in March. It takes three inputs: SUMIF(range to check, criteria, range to add).
AVERAGEIF takes the same three and gives the mean of the matching rows.
Worked example: SUMIF
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Team | Region | Hours | Sales hours | ||
| 2 | Amira | Sales | North | 10 | Sales average | ||
| 3 | Ben | Finance | South | 6 | Sales in North | ||
| 4 | Cara | Sales | South | 14 | |||
| 5 | Dev | Support | North | 8 | |||
| 6 | Ewa | Sales | North | 4 |
=SUMIF(B2:B6in . First, the range to check: the Team column.=SUMIF(B2:B6,"Sales"in . Then the criteria: what to look for in it, in quotes, as with COUNTIF.=SUMIF(B2:B6,"Sales",D2:D6)in . Last, the range to add: the Hours column. Only the Sales rows are added: 10 + 14 + 4 = 28.
AVERAGEIF and SUMIFS
AVERAGEIFS works the same way as SUMIFS: the range to average first, then the pairs.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Team | Region | Hours | Sales hours | ||
| 2 | Amira | Sales | North | 10 | Sales average | ||
| 3 | Ben | Finance | South | 6 | Sales in North | ||
| 4 | Cara | Sales | South | 14 | |||
| 5 | Dev | Support | North | 8 | |||
| 6 | Ewa | Sales | North | 4 |
=AVERAGEIF(B2:B6,"Sales",D2:D6)in G2. AVERAGEIF takes the same three inputs and gives the mean of the matching rows: 28 ÷ 3 = 9.33.=SUMIFS(D2:D6,B2:B6,"Sales",C2:C6,"North")in G3. For more than one condition, use SUMIFS. Careful: the range to add comes first, then pairs of range and criteria. Sales and North: Amira and Ewa, 10 + 4 = 14.
Summary tables
To total each team in a table, put the team names in a column and use the name next to the formula as the criteria: =SUMIF($B$2:$B$6,F5,$D$2:$D$6).
When it's copied down, F5 moves to the next team's name, and the locked ranges stay on the data. Forget the $ signs and the ranges slide down off the data.
What you'll practise
- In H2, total the training hours for the North region.
- In H3, find the average attendance of the Support team.
- In H4, total the training hours of employees in Sales and the South region.
- In H5, find the average training hours of Finance employees whose attendance is at least 90%.
- Make a summary table. In H2, total the sales of the team named in G2. Your formula will be copied down to H5 for the other teams.