← VCE Spreadsheets, SQL & Visualisation

The SUMIF function

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.

Part of: VCE Applied Computing - U1O1 - Data analysis — Module 15, VCE Data Analytics - U3O1 - Data Analytics — Module 68, VCE Data Analytics - U4O1 - Development and Evaluation — Module 12, 7-8 Digital Technologies: Analysing and visualising data — Module 7

Understanding The SUMIF function in VCE Spreadsheets, SQL & Visualisation

What it does: The criteria range holds the team names, the criteria is Red, and the sum range holds the points to add up. D2:D8 is checked for Sport, and the matching numbers in E2:E8 are added, so the two ranges do different jobs. SUMIF checks a condition before adding. SUM adds everything and has no criteria. The sum_range holds the numbers. Only the ones in the same rows as a match are added. The range with the category (sport) comes first. The numbers to add (points) come last. Counting matching rows is the job of COUNTIF. SUMIF adds up the values. Remember: where to look, what to look for, then what to add.

Common mistakes: The criteria is text, so it must be inside quotation marks. It should be =SUMIF(A2:A8,"Food",B2:B8). 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.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add The SUMIF function to a class →

This module's key terms are also in a printable crossword (Modules 4–20) and word search (Modules 4–34).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
Which formula adds up only the amounts in B2:B10 where column A says "Food"?
=SUM(A2:A10,"Food",B2:B10)=SUMIF(A2:A10,"Food",B2:B10)
Matches
=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.
Applying
Meanings
Term
=SUMIF(range, criteria, sum_range)
Meaning
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.
Practice
=SUMIF(B2:B20,"Food",C2:C20)
Testing
Gaps
=(A2:A10,"Fruit",B2:B10)
Check Answer
Spots
2B2:B8 = Sales
3=SUMIF(A2:A8,Food,B2:B8)

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Reef Shark' badge — Reef Ruler, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

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)
SUMIF checks a condition before adding. SUM adds everything and has no criteria.
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)
The first argument is the range that holds the categories. The criteria is tested against these cells.
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
The sum_range holds the numbers. Only the ones in the same rows as a match are added.
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) ✓
The range with the category (sport) comes first. The numbers to add (points) come last.
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. ✓
Counting matching rows is the job of COUNTIF. SUMIF adds up the values.

Match the Pairs

=SUMIF(A2:A10,"Pizza",B2:B10) → Adds up only the amounts in B2:B10 where the matching cell in A2:A10 says Pizza
=SUMIF(C2:C20,">50",C2:C20) → Adds up only the values in C2:C20 that are greater than 50
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)
The criteria range is the cells SUMIF checks, and the sum range is the cells it adds up → =SUMIF(D2:D8,"Sport",E2:E8)
=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.
=SUMIF(A2:A10,Pizza,B2:B10) → The text criteria Pizza is missing quote marks, so the spreadsheet does not read it as text.

Key Term

=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.

Fill in the Blanks

1. =SUMIF(A2:A10,"Fruit",B2:B10)
Hint: Type the function name that adds only the values that meet a condition.
2. =SUMIF(A2:A10,"Fruit",B2:B10)
Hint: Type the criteria range: the cells that are checked against the condition.
3. =SUMIF(A2:A10,"Fruit",B2:B10)
Hint: Type the sum range: the cells holding the numbers to be added.
4. =SUMIF(C2:C20,"Netball",D2:D20)
Hint: Type the symbol that every spreadsheet formula must start with.
5. =SUMIF(A2:A10,F1,B2:B10)
Hint: Type the cell reference that holds the category to match, instead of typing the text.

Spot the Error

Question 1
A A2:A8 = Category
B B2:B8 = Sales
C =SUMIF(A2:A8,Food,B2:B8)
D Result: total Food sales
Line C — The criteria is text, so it must be inside quotation marks. It should be =SUMIF(A2:A8,"Food",B2:B8).
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)
Line 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.
Question 3
A Total Drinks sales go in D2
B =SUMIF(A2:A9,"Drinks",B2:B12)
C Only the Drinks rows are added
Line 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.
Question 4
A =SUMIF(A2:A9,"Fruit")
B A = Item, B = Cost
C Result: total cost of Fruit
Line 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).
Question 5
A =COUNTIF(A2:A9,"Pets",B2:B9)
B A = Product type, B = Price
C Result: total price of Pets
Line A — COUNTIF counts matching cells and does not add values from a sum_range. To add the prices of Pets, use SUMIF.

Student with a class code? Join your class →