Confident Cells

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.

Your turn

In H2, total the training hours for the North region.

ABCDEFGH
1EmployeeTeamRegionAttendanceTraining hours
2GraceSalesNorth97%64North region hours
3TomFinanceSouth88%22Support average attendance91.4%
4PriyaSalesEast99%81Sales South region hours46
5MarekSupportNorth91%35Finance average hours (90%+ attendance)58.667
6NiaFinanceEast95%58
7OllieSupportSouth84%17
8LucySalesSouth93%46
9HamzaFinanceNorth89%29
10RachelSupportEast98%73
11SunilSalesNorth86%31
12TanyaFinanceSouth96%52
13WillSupportNorth94%40
14ZoeSalesEast92%49
15BenFinanceNorth97%66
16EllaSupportSouth90%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

ABCDEFG
1NameTeamRegionHoursSales hours
2AmiraSalesNorth10Sales average
3BenFinanceSouth6Sales in North
4CaraSalesSouth14
5DevSupportNorth8
6EwaSalesNorth4
  1. =SUMIF(B2:B6 in . First, the range to check: the Team column.
  2. =SUMIF(B2:B6,"Sales" in . Then the criteria: what to look for in it, in quotes, as with COUNTIF.
  3. =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.

ABCDEFG
1NameTeamRegionHoursSales hours
2AmiraSalesNorth10Sales average
3BenFinanceSouth6Sales in North
4CaraSalesSouth14
5DevSupportNorth8
6EwaSalesNorth4
  1. =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.
  2. =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

  1. In H2, total the training hours for the North region.
  2. In H3, find the average attendance of the Support team.
  3. In H4, total the training hours of employees in Sales and the South region.
  4. In H5, find the average training hours of Finance employees whose attendance is at least 90%.
  5. 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.