The SUMIF function

Tip: Choose Save as PDF in the print dialog to download. Use A4 paper size.

The SUMIF function

Name:
Date:
Class:
A

Quick Questions

Shade in the A or B circle for each question.

1. Which formula adds up only the amounts in B2:B10 where column A says "Food"?
A =SUMIF(A2:A10,"Food",B2:B10)
B =SUM(A2:A10,"Food",B2:B10)
2. In =SUMIF(A2:A10,"Drinks",B2:B10), which cells does Excel check against "Drinks"?
A A2:A10 (the range)
B B2:B10 (the sum_range)
3. In =SUMIF(A2:A10,"Drinks",B2:B10), which cells are actually added up?
A The cells in B2:B10 that sit beside a match
B Every cell in A2:A10
4. Your table lists the sport in column C and the points in column D. Which formula totals the points for "Netball"?
A =SUMIF(D2:D20,"Netball",C2:C20)
B =SUMIF(C2:C20,"Netball",D2:D20)
5. True or false: SUMIF adds up the number of rows that match the criteria.
A True. It counts the matching rows.
B False. It adds up the values in the sum_range for matching rows.
B

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left columnRight column
1=SUMIF(A2:A10,"Pizza",B2:B10) AThe arguments are in the wrong order. The criteria must be second and the sum range must be third.
2=SUMIF(C2:C20,">50",C2:C20) BAdds up only the amounts in B2:B10 where the matching cell in A2:A10 says Pizza
3Find the total points scored by only the Red team, with team names in A2:A12 and points in B2:B12 C=SUMIF(D2:D8,"Sport",E2:E8)
4The criteria range is the cells SUMIF checks, and the sum range is the cells it adds up DThe text criteria Pizza is missing quote marks, so the spreadsheet does not read it as text.
5=SUMIF(A2:A10,B2:B10,"Pizza") E=SUMIF(A2:A12,"Red",B2:B12)
6=SUMIF(A2:A10,Pizza,B2:B10) FAdds up only the values in C2:C20 that are greater than 50
C

Definitions

=SUMIF(range, criteria, sum_range)
D

Fill in the Blanks

Complete each line of code by filling in the missing parts.

1.
=_____(A2:A10,"Fruit",B2:B10)
2.
=SUMIF(______,"Fruit",B2:B10)
3.
=SUMIF(A2:A10,"Fruit",______)
4.
_SUMIF(C2:C20,"Netball",D2:D20)
5.
=SUMIF(A2:A10,__,B2:B10)
E

Spot the Error

Shade in the letter of the line that contains an error.

Question 1
A A2:A8 = Category
B B2:B8 = Sales
C =SUMIF(A2:A8,Food,B2:B8)
D Result: total Food sales
Question 2
A A2:A9 holds the team colour
B B2:B9 holds the points
C Goal: total points for Red
D =SUMIF(B2:B9,"Red",A2:A9)
Question 3
A Total Drinks sales go in D2
B =SUMIF(A2:A9,"Drinks",B2:B12)
C Only the Drinks rows are added
Question 4
A =SUMIF(A2:A9,"Fruit")
B A = Item, B = Cost
C Result: total cost of Fruit
Question 5
A =COUNTIF(A2:A9,"Pets",B2:B9)
B A = Product type, B = Price
C Result: total price of Pets

Answer Key

The SUMIF function
Section A: Quick Questions
Q1
A — =SUMIF(A2:A10,"Food",B2:B10)
Q2
A — A2:A10 (the range)
Q3
A — The cells in B2:B10 that sit beside a match
Q4
B — =SUMIF(C2:C20,"Netball",D2:D20)
Q5
B — False. It adds up the values in the sum_range for matching rows.
Section B: Match the Pairs
Q1
=SUMIF(A2:A10,"Pizza",B2:B10) → Adds up only the amounts in B2:B10 where the matching cell in A2:A10 says Pizza
Q2
=SUMIF(C2:C20,">50",C2:C20) → Adds up only the values in C2:C20 that are greater than 50
Q3
Find the total points scored by only the Red team, with team names in A2:A12 and points in B2:B12 → =SUMIF(A2:A12,"Red",B2:B12)
Q4
The criteria range is the cells SUMIF checks, and the sum range is the cells it adds up → =SUMIF(D2:D8,"Sport",E2:E8)
Q5
=SUMIF(A2:A10,B2:B10,"Pizza") → The arguments are in the wrong order. The criteria must be second and the sum range must be third.
Q6
=SUMIF(A2:A10,Pizza,B2:B10) → The text criteria Pizza is missing quote marks, so the spreadsheet does not read it as text.
Section C: Definitions
Q1
=SUMIF(range, criteria, sum_range) — Adds only the numbers in sum_range whose matching cell in range meets the criteria, such as the total of all sales for one category.
Section D: Fill in the Blanks
Q1
=SUMIF(A2:A10,"Fruit",B2:B10)
Q2
=SUMIF(A2:A10,"Fruit",B2:B10)
Q3
=SUMIF(A2:A10,"Fruit",B2:B10)
Q4
=SUMIF(C2:C20,"Netball",D2:D20)
Q5
=SUMIF(A2:A10,F1,B2:B10)
Section E: Spot the Error
Q1
C — The criteria is text, so it must be inside quotation marks. It should be =SUMIF(A2:A8,"Food",B2:B8).
Q2
D — The range and sum_range are swapped. The criteria range (team colour, A2:A9) comes first and the values to add (points, B2:B9) come last.
Q3
B — The sum_range B2:B12 is a different size from the criteria range A2:A9. Both ranges must cover the same rows, so it should be B2:B9.
Q4
A — The sum_range is missing, so Excel does not know which column of costs to add. It should be =SUMIF(A2:A9,"Fruit",B2:B9).
Q5
A — COUNTIF counts matching cells and does not add values from a sum_range. To add the prices of Pets, use SUMIF.