← Years 9–10 Data & Information

COUNTIF and SUMIF

COUNTIF counts the cells in a range that meet a condition. SUMIF works the same way but adds up the values that meet it. Example: count every 'Yes' in C2:C50.

Part of: 9-10 Digital Technologies: Acquiring, storing and validating data — Module 13, ACARA Digital Technologies Years 7–8 — Acquiring and Storing Data — Module 52, ACARA Digital Technologies Years 7–8 — Analysing and Visualising Data — Module 29, ACARA Digital Technologies Years 9–10 — Analysing and Visualising Data — Module 20

Understanding COUNTIF and SUMIF in Years 9–10 Data & Information

What it does: A number condition such as ">10" goes in quote marks and COUNTIF counts the cells that meet it. The condition is tested on A2:A20 and the sum range C2:C20 holds the values that get added. The third argument is the sum range. Without C2:C20, SUMIF would try to add up the cells in B2:B20, which hold text, not prices. The first range (A2:A50) is where Excel checks the condition. The last range (D2:D50) is the sum range holding the numbers to add. Text conditions need quotation marks around them. Without quotes, the spreadsheet thinks Yes is a name and gives an error.

Common mistakes: Text conditions in COUNTIF must be inside double quotes. It should be =COUNTIF(C2:C50,"Yes"). COUNT only counts numbers and does not take a condition. To count cells above 10 you need COUNTIF: =COUNTIF(D2:D50,">10").

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 COUNTIF and SUMIF to a class →

This module's key terms are also in a printable crossword (Modules 17–51) and word search (Modules 6–132).

Take this worksheet further — free with Cloodit

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

Learning
Duo
Which formula counts how many cells in C2:C50 contain the word Yes?
=COUNTIF(C2:C50,"Yes")=SUMIF(C2:C50,"Yes")
Matches
=COUNTIF(C2:C50,Yes)
↓
The text condition Yes is missing its quote marks. It should be "Yes".
Applying
Meanings
Term
=COUNTIF(C2:C50,"Yes")
Meaning
COUNTIF counts the cells in a range that meet a condition. SUMIF works the same way but adds up the values that meet it. Example: count every 'Yes' in C2:C50.
Practice
=SUMIF(B2:B50,"Canteen",D2:D50)
Testing
Gaps
=(C2:C50,"Yes")
Check Answer
Spots
1E1: =COUNTIF(C2:C50,Yes)
2E2: =COUNTIF(C2:C50,"No")

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

The 'Galaxy Phoenix' badge — Orbit Champion, 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 counts how many cells in C2:C50 contain the word Yes?
A. =COUNTIF(C2:C50,"Yes") ✓
B. =SUMIF(C2:C50,"Yes")
COUNTIF counts the cells that match a condition. SUMIF adds up values, so it is the wrong choice when you only want a count of 'Yes' answers.
2. Which formula counts how many scores in B2:B30 are greater than 10?
A. =COUNTIF(B2:B30,">10") ✓
B. =COUNTIF(B2:B30,>10)
A condition with an operator such as > must go inside quotation marks, like ">10". Without the quotes the formula is invalid.
3. True or false: =COUNTIF(D2:D40,"Pass") adds up the numbers in D2:D40.
A. False: it counts the cells that contain Pass ✓
B. True: it adds up the numbers
COUNTIF only counts how many cells meet the condition. It never adds values together. SUMIF is the function that adds.
4. A table has prices in C2:C20 and the product type in B2:B20. Which formula adds up the prices of every 'Hoodie'?
A. =SUMIF(B2:B20,"Hoodie")
B. =SUMIF(B2:B20,"Hoodie",C2:C20) ✓
The third argument is the sum range. Without C2:C20, SUMIF would try to add up the cells in B2:B20, which hold text, not prices.
5. In =SUMIF(A2:A50,"Canteen",D2:D50), which range holds the values that get added?
A. A2:A50
B. D2:D50 ✓
The first range (A2:A50) is where Excel checks the condition. The last range (D2:D50) is the sum range holding the numbers to add.

Match the Pairs

=COUNTIF(C2:C50,"Yes") → Counts how many cells in C2:C50 contain the text Yes
=SUMIF(B2:B30,">10",C2:C30) → Adds up the values in C2:C30 where the matching cell in B2:B30 is greater than 10
Count how many scores in B2:B40 are greater than 10 → =COUNTIF(B2:B40,">10")
Add up the Sales in C2:C20 for rows where column A says Pizza → =SUMIF(A2:A20,"Pizza",C2:C20)
=COUNTIF(C2:C50,Yes) → The text condition Yes is missing its quote marks. It should be "Yes".
=SUMIF(B2:B30,C2:C30,">10") → The arguments are in the wrong order. The condition must come second and the sum range third.

Key Term

=COUNTIF(C2:C50,"Yes")
COUNTIF counts the cells in a range that meet a condition. SUMIF works the same way but adds up the values that meet it. Example: count every 'Yes' in C2:C50.

Fill in the Blanks

1. =COUNTIF(C2:C50,"Yes")
Hint: Type the function that counts the cells meeting a condition.
2. =COUNTIF(B2:B30,">10")
Hint: Type the comparison operator that means greater than.
3. =SUMIF(A2:A20,"Food",B2:B20)
Hint: Type the function that adds up values meeting a condition.
4. =SUMIF(A2:A20,"Food",B2:B20)
Hint: Type the sum range, which holds the values to add up.
5. =COUNTIF(C2:C50,"Yes")
Hint: Type the range of cells that the condition is checked against.

Spot the Error

Question 1
A E1: =COUNTIF(C2:C50,Yes)
B E2: =COUNTIF(C2:C50,"No")
C E3: =E1+E2
Line A — Text conditions in COUNTIF must be inside double quotes. It should be =COUNTIF(C2:C50,"Yes").
Question 2
A =COUNT(D2:D50,">10")
B =COUNTIF(D2:D50,"<5")
C =COUNTIF(D2:D50,"<=5")
Line A — COUNT only counts numbers and does not take a condition. To count cells above 10 you need COUNTIF: =COUNTIF(D2:D50,">10").
Question 3
A =SUMIF(B2:B30,"Hats",C2:C30)
B =SUMIF(B2:B30,"Caps",C2:C30)
C =SUMIF(B2:B30,C2:C30,"Bags")
Line C — SUMIF needs the range to test, then the condition, then the sum range. The condition and sum range are swapped. It should be =SUMIF(B2:B30,"Bags",C2:C30).
Question 4
A =COUNTIF(C2:C50,"Yes")
B =COUNTIF(C2:C50,"No")
C =COUNTIF(D2:D50,">10")
D =SUMIF(D2:D50,">10,D2:D50)
Line D — The closing quote after >10 is missing, so the condition is not a complete piece of text. It should be =SUMIF(D2:D50,">10",D2:D50).
Question 5
A =COUNTIF(B2:B31,"Present")
B =COUNTIF(B2:B31,"Absent"
C =COUNTIF(B2:B31,"Late")
Line B — The closing bracket is missing, so the formula is incomplete. It should be =COUNTIF(B2:B31,"Absent").

Student with a class code? Join your class →