← ACARA Digital Technologies Years 9–10

COUNTIFS and SUMIFS with several criteria

Counts only rows that meet two or more criteria at once, using pairs of criteria range and criterion. SUMIFS works the same way but adds numbers, and its sum_range comes first.

Part of: ACARA Digital Technologies Years 9–10 — Analysing and Visualising Data — Module 22

Understanding COUNTIFS and SUMIFS with several criteria in ACARA Digital Technologies Years 9–10

What it does: Each condition is a pair: the range to check (B2:B9) then the test (">=50"). Two pairs mean two conditions must both be true. COUNTIFS takes pairs: a criteria range followed by its criterion. Here A:A goes with "Year 9" and B:B goes with ">50". COUNTIFS works like AND. Every criterion must be true for a row to be counted. All the ranges in COUNTIFS must be the same size. A2:A9 has 8 rows but B2:B8 has only 7, so Excel shows #VALUE!. The correct version uses B2:B9. COUNTIFS counts rows and never adds values. To add numbers that meet criteria, use SUMIFS.

Common mistakes: The second criterion has no criteria range in front of it. COUNTIFS needs pairs of range and criterion, so it should be =COUNTIFS(A:A,"Y9",B:B,">50"). SUMIFS puts the sum_range first, not last. The points column must come first: =SUMIFS(C:C,A:A,"Y9",B:B,F1).

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 COUNTIFS and SUMIFS with several criteria to a class →

This module's key terms are also in a printable crossword (Modules 333–377) and word search (Modules 333–412).

Take this worksheet further — free with Cloodit

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

Learning
Duo
Which formula counts students in Year 9 who scored over 50?
=COUNTIFS(A:A,"Year 9",B:B,">50")=COUNTIFS(A:A,B:B,"Year 9",">50")
Matches
=SUMIFS(A2:A9,"Year 9",C2:C9,B2:B9,">50")
↓
The criteria range and criterion pairs are mixed up: "Year 9" has no range before it and C2:C9 is used as a criterion. Fix it as =SUMIFS(C2:C9,A2:A9,"Year 9",B2:B9,">50")
Applying
Meanings
Term
COUNTIFS
Meaning
Counts only rows that meet two or more criteria at once, using pairs of criteria range and criterion. SUMIFS works the same way but adds numbers, and its sum_range comes first.
Practice
=COUNTIFS(B2:B9,"Tech",C2:C9,">10")
Testing
Gaps
=(A:A,"Year 9",B:B,">50")
Check Answer
Spots
1Count Y9 students over 50
2=COUNTIFS(A:A,"Y9",">50")

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 counts students in Year 9 who scored over 50?
A. =COUNTIFS(A:A,"Year 9",B:B,">50") ✓
B. =COUNTIFS(A:A,B:B,"Year 9",">50")
COUNTIFS takes pairs: a criteria range followed by its criterion. Here A:A goes with "Year 9" and B:B goes with ">50".
2. A row is counted by COUNTIFS with two criteria pairs when...
A. It meets AT LEAST ONE criterion
B. It meets BOTH criteria ✓
COUNTIFS works like AND. Every criterion must be true for a row to be counted.
3. Which argument comes FIRST in SUMIFS?
A. sum_range (the numbers to add) ✓
B. criteria_range1 (the cells to test)
SUMIFS puts sum_range first. After that come the pairs of criteria range and criterion.
4. Which formula adds the Qty (column D) for Tech items (column B) with Price (column C) over 10?
A. =SUMIFS(D2:D9,B2:B9,"Tech",C2:C9,">10") ✓
B. =SUMIFS(B2:B9,"Tech",C2:C9,">10",D2:D9)
The range you want to add (D2:D9) must come first. The criteria pairs follow it.
5. Which formula has a mistake?
A. =COUNTIFS(A2:A9,"Year 9",B2:B9,">50")
B. =COUNTIFS(A2:A9,"Year 9",B2:B8,">50") ✓
All the ranges in COUNTIFS must be the same size. A2:A9 has 8 rows but B2:B8 has only 7, so Excel shows #VALUE!. The correct version uses B2:B9.

Match the Pairs

=COUNTIFS(A:A,"Year 9",B:B,">50") → Counts the rows where column A is Year 9 AND column B is greater than 50
=SUMIFS(C:C,A:A,"Year 9",B:B,">50") → Adds up column C only for rows where column A is Year 9 AND column B is greater than 50
COUNTIFS needs a criteria range followed by its criterion, repeated for each condition → =COUNTIFS(B2:B9,">=50",C2:C9,"<80")
In SUMIFS the range of numbers to add comes first, before the criteria pairs → =SUMIFS(D2:D9,B2:B9,"Tech",D2:D9,">10")
=SUMIFS(A2:A9,"Year 9",C2:C9,B2:B9,">50") → The criteria range and criterion pairs are mixed up: "Year 9" has no range before it and C2:C9 is used as a criterion. Fix it as =SUMIFS(C2:C9,A2:A9,"Year 9",B2:B9,">50")
=COUNTIFS(A2:A9,"Year 9",B2:B8,">50") → The two criteria ranges are different sizes (A2:A9 and B2:B8). Fix it as =COUNTIFS(A2:A9,"Year 9",B2:B9,">50")

Key Term

COUNTIFS
Counts only rows that meet two or more criteria at once, using pairs of criteria range and criterion. SUMIFS works the same way but adds numbers, and its sum_range comes first.

Fill in the Blanks

1. =COUNTIFS(A:A,"Year 9",B:B,">50")
Hint: Type the function that counts only rows meeting two or more criteria at once.
2. =COUNTIFS(A:A,"Year 9",B:B,">50")
Hint: Type the comparison operator that means greater than.
3. =SUMIFS(C:C,A:A,"Year 9",B:B,">50")
Hint: Type the function that adds numbers only in rows meeting several criteria.
4. =SUMIFS(C:C,A:A,"Year 9",B:B,">50")
Hint: Type the sum_range, the column of numbers to add, which always comes first.
5. =SUMIFS(D2:D9,B2:B9,"Tech",C2:C9,">10")
Hint: Type the criteria range that is paired with the second criterion, the price test.

Spot the Error

Question 1
A Count Y9 students over 50
B =COUNTIFS(A:A,"Y9",">50")
C Result is the number of rows
Line B — The second criterion has no criteria range in front of it. COUNTIFS needs pairs of range and criterion, so it should be =COUNTIFS(A:A,"Y9",B:B,">50").
Question 2
A A=year, B=score, C=points
B F1 holds ">50"; add points
C =SUMIFS(A:A,"Y9",B:B,F1,C:C)
Line C — SUMIFS puts the sum_range first, not last. The points column must come first: =SUMIFS(C:C,A:A,"Y9",B:B,F1).
Question 3
A A=house, B=points
B Count Red with points above 90
C =COUNTIFS(A:A,"Red",B:B,>90)
Line C — A criterion that includes an operator must be in quotes. It should be ">90", not >90.
Question 4
A Count Y9 rows scoring over 80
B =COUNTIF(A:A,"Y9",B:B,">80")
C Both tests must be true
Line B — COUNTIF only handles one criterion. To test two criteria at once, use COUNTIFS.
Question 5
A A=item B=category C=price
B D=qty and F1 holds "<20"
C Sum qty of cheap Tech items
D =SUMIFS(D:D,B:B,"Tech",C:C)
Line D — The last criteria range, C:C, has no criterion after it. Every range needs a criterion, so add F1: =SUMIFS(D:D,B:B,"Tech",C:C,F1).

Student with a class code? Join your class →