← VCE Spreadsheets, SQL & Visualisation

The COUNTIF function

Counts how many cells in a range match a value or condition, such as "Yes" or ">10". Example: =COUNTIF(C2:C30,"Yes") counts every Yes in C2 to C30.

Part of: VCE Applied Computing - U1O1 - Data analysis — Module 14, VCE Data Analytics - U3O1 - Data Analytics — Module 67, VCE Data Analytics - U4O1 - Development and Evaluation — Module 21, 7-8 Digital Technologies: Analysing and visualising data — Module 6, ACARA Digital Technologies Years 9–10 — Acquiring and Validating Data — Module 98

Understanding The COUNTIF function in VCE Spreadsheets, SQL & Visualisation

What it does: The range is D2:D25 and the criteria is the text "No", so COUNTIF counts every No. The condition "<50" goes in quotes as the criteria, so only scores below 50 are counted. COUNTIF counts cells that match a condition. COUNT only counts cells that hold numbers, so it can't look for the word Yes. The range is the group of cells to look in. "Yes" is the criteria, the thing each cell is checked against. The range always comes first, then a comma, then the criteria. The > sign means greater than, so a cell with 10 is not counted. Only cells with 11 or more are counted.

Common mistakes: The arguments are the wrong way around. COUNTIF needs the range first and the criteria second: =COUNTIF(C2:C30,"Yes"). COUNTIF needs a criteria argument as well as a range. It should be =COUNTIF(C2:C20,">10") to count the cells greater than 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 The COUNTIF 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 counts how many cells in C2:C30 contain the word Yes?
=COUNTIF(C2:C30,"Yes")=COUNT(C2:C30,"Yes")
Matches
=COUNTIF(C2:C30,Yes)
↓
The text criteria Yes is missing quote marks. It should be "Yes".
Applying
Meanings
Term
=COUNTIF(range, criteria)
Meaning
Counts how many cells in a range match a value or condition, such as "Yes" or ">10". Example: =COUNTIF(C2:C30,"Yes") counts every Yes in C2 to C30.
Practice
=COUNTIF(C2:C30,"Yes")
Testing
Gaps
=(C2:C30,"Yes")
Check Answer
Spots
1=COUNTIF("Yes",C2:C30)
2=COUNTIF(D2:D30,">10")

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

The 'Desert Tortoise' badge — Desert Master, 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:C30 contain the word Yes?
A. =COUNTIF(C2:C30,"Yes") ✓
B. =COUNT(C2:C30,"Yes")
COUNTIF counts cells that match a condition. COUNT only counts cells that hold numbers, so it can't look for the word Yes.
2. What does =COUNTIF(B2:B20,">10") do?
A. Counts the cells in B2:B20 that are bigger than 10 ✓
B. Adds up all the numbers in B2:B20 that are bigger than 10
COUNTIF counts how many cells match. SUMIF is the function that adds up matching values.
3. Which part of =COUNTIF(C2:C30,"Yes") is the range?
A. "Yes"
B. C2:C30 ✓
The range is the group of cells to look in. "Yes" is the criteria, the thing each cell is checked against.
4. A class survey has Yes and No answers in D2:D25. Which formula counts the No answers?
A. =COUNTIF(D2:D25,"No") ✓
B. =COUNTIF("No",D2:D25)
The range always comes first, then a comma, then the criteria.
5. True or false: =COUNTIF(A1:A10,">10") counts cells that are exactly 10.
A. True
B. False ✓
The > sign means greater than, so a cell with 10 is not counted. Only cells with 11 or more are counted.

Match the Pairs

=COUNTIF(C2:C30,"Yes") → Counts how many cells from C2 to C30 contain the word Yes
=COUNTIF(B2:B20,">10") → Counts how many cells from B2 to B20 hold a number greater than 10
Count how many students answered No in cells D2 to D25 → =COUNTIF(D2:D25,"No")
Count how many test scores in A2:A30 are less than 50 → =COUNTIF(A2:A30,"<50")
=COUNTIF(C2:C30,Yes) → The text criteria Yes is missing quote marks. It should be "Yes".
=COUNTIF(B2:B20,>10) → The condition >10 is missing quote marks. It should be ">10".

Key Term

=COUNTIF(range, criteria)
Counts how many cells in a range match a value or condition, such as "Yes" or ">10". Example: =COUNTIF(C2:C30,"Yes") counts every Yes in C2 to C30.

Fill in the Blanks

1. =COUNTIF(C2:C30,"Yes")
Hint: Type the function name that counts cells matching a condition.
2. =COUNTIF(C2:C30,"Yes")
Hint: Type the symbol that joins the first and last cell of a range.
3. =COUNTIF(B2:B30,">10")
Hint: Type the comparison operator that means greater than.
4. =COUNTIF(B2:B30,"<5")
Hint: Type the comparison operator that means less than.
5. =COUNTIF(B2:B30,">=50")
Hint: Type the symbol that every spreadsheet formula starts with.

Spot the Error

Question 1
A =COUNTIF("Yes",C2:C30)
B =COUNTIF(D2:D30,">10")
C =COUNTIF(E2:E30,"No")
Line A — The arguments are the wrong way around. COUNTIF needs the range first and the criteria second: =COUNTIF(C2:C30,"Yes").
Question 2
A =COUNTIF(B2:B20,"Yes")
B =COUNTIF(C2:C20)
C =COUNTIF(D2:D20,"No")
Line B — COUNTIF needs a criteria argument as well as a range. It should be =COUNTIF(C2:C20,">10") to count the cells greater than 10.
Question 3
A A1: Pet
B A2:A30 = Dog, Cat, Fish
C B1: Number of dogs
D B2: =COUNT(A2:A30,"Dog")
Line D — COUNT only counts cells that hold numbers and does not take a condition like "Dog". To count matching text you need COUNTIF.
Question 4
A A1: Test scores in B2:B30
B A2: =COUNTIF(B2:B30,"=>50")
C A3: =COUNTIF(B2:B30,"<50")
Line B — The operator is written the wrong way round. "Greater than or equal to" is >=, not =>. It should be ">=50".
Question 5
A A1: Goals scored
B B2:B20 holds goal numbers
C C1: =COUNTIF(B2:B20,">3)
Line C — The criteria is missing its closing quote mark. A condition such as ">3" must have quotes at both ends.

Student with a class code? Join your class →