← VCE Spreadsheets, SQL & Visualisation

Building a frequency table with COUNTIF

Used in a frequency table: list each category in column A, then put this beside it to count how many times that category appears in the data range. Copy it down for every category.

Part of: VCE Applied Computing - U1O1 - Data analysis — Module 20, VCE Data Analytics - U3O1 - Data Analytics — Module 84, VCE Data Analytics - U3O2 - Analysis and Design — Module 65, VCE Data Analytics - U4O1 - Development and Evaluation — Module 22, ACARA Digital Technologies Years 9–10 — Acquiring and Validating Data — Module 25, ACARA Digital Technologies Years 9–10 — Cyber Security Threats and Vulnerabilities — Module 111

Understanding Building a frequency table with COUNTIF in VCE Spreadsheets, SQL & Visualisation

What it does: A2 holds one unique category, and the COUNTIF formula in B2 refers to A2 to count how many times it appears in the data range. SUM adds the frequencies and COUNTA counts every entry in the data. If the result is TRUE, every value has been counted in a category. The range to search comes first and the criteria (the category cell) comes second. Each category must be listed once only. The count column shows how often it occurs. A2 is a relative reference, so it moves down. The locked range $D$2:$D$31 stays the same for every row. Checking that the counts add up to the total helps find missing categories, typos or extra spaces.

Common mistakes: COUNT only counts numbers and has no criteria argument. A frequency table needs =COUNTIF($D$2:$D$31,A2) to count how many times the category in A2 appears. The formula beside Train must use A3 as its criteria. Using A2 counts Bus again, so Train's count is wrong. It should be =COUNTIF($D$2:$D$31,A3).

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 Building a frequency table with COUNTIF 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
What goes in column A of a frequency table built with COUNTIF?
A list of each unique category, written onceEvery value from the data, repeated as many times as it appears
Matches
=COUNTIF(D2:D21,A2) copied down without $ signs
↓
The range is relative, so it shifts down each row when copied and misses data, giving wrong counts
Applying
Meanings
Term
=COUNTIF(range,A2)
Meaning
Used in a frequency table: list each category in column A, then put this beside it to count how many times that category appears in the data range. Copy it down for every category.
Practice
=COUNTIF($C$2:$C$31,A2)
Testing
Gaps
=($B$2:$B$31,D2)
Check Answer
Spots
1A2: Bus
2B2: =COUNT($D$2:$D$31,A2)

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

The 'Pufferfish' badge — Error Handler, 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. What goes in column A of a frequency table built with COUNTIF?
A. A list of each unique category, written once ✓
B. Every value from the data, repeated as many times as it appears
A frequency table lists each category once in column A. COUNTIF in column B then counts how often it appears.
2. Which formula in B2 counts how many times the category in A2 appears in the data range D2:D31?
A. =COUNTIF(D2:D31,A2) ✓
B. =COUNTIF(A2,D2:D31)
The range to search comes first and the criteria (the category cell) comes second.
3. A frequency table has these categories: Red, Blue, Green, Red. Is this list set up correctly?
A. Yes, every category should be listed as often as it appears
B. No, Red is listed twice, so it should only appear once ✓
Each category must be listed once only. The count column shows how often it occurs.
4. You copy =COUNTIF($D$2:$D$31,A2) from B2 down to B3. What happens to the criteria?
A. It becomes A3, so the next category is counted ✓
B. It stays as A2, so the same category is counted again
A2 is a relative reference, so it moves down. The locked range $D$2:$D$31 stays the same for every row.
5. Your frequency table counts add up to 28, but the data has 30 entries. What is the best conclusion?
A. The table is fine because totals do not need to match the data
B. Something is wrong, such as a missing category or a spelling mismatch ✓
Checking that the counts add up to the total helps find missing categories, typos or extra spaces.

Match the Pairs

=COUNTIF($B$2:$B$31,A2) → Counts how many times the category in A2 appears in the data range B2:B31
=SUM(B2:B5) → Adds up all the counts to check they equal the total number of data entries
Column A lists each unique category once, and the cell beside it counts how often that category occurs → A2: Red B2: =COUNTIF($D$2:$D$21,A2)
Check the table is complete by comparing the sum of the counts with the total number of data values → =SUM(B2:B5)=COUNTA($D$2:$D$21)
=COUNTIF(D2:D21,A2) copied down without $ signs → The range is relative, so it shifts down each row when copied and misses data, giving wrong counts
=COUNTIF(A2,$D$2:$D$21) → The arguments are in the wrong order: the range must come first and the category (criteria) second

Key Term

=COUNTIF(range,A2)
Used in a frequency table: list each category in column A, then put this beside it to count how many times that category appears in the data range. Copy it down for every category.

Fill in the Blanks

1. =COUNTIF($B$2:$B$31,D2)
Hint: Type the function name that counts how many cells in a range match a category.
2. =COUNTIF($B$2:$B$31,D2)
Hint: Type the cell reference of the category being counted, which is the criteria argument.
3. =COUNTIF($B$2:$B$31,D3)
Hint: Type the fixed data range that holds every recorded category, with absolute references.
4. =SUM(E2:E6)
Hint: Type the function that adds up all the counts in the frequency table.
5. =SUM(E2:E6)=COUNTA(B2:B31)
Hint: Type the comparison operator that checks the counts total equals the number of records.

Spot the Error

Question 1
A A2: Bus
B B2: =COUNT($D$2:$D$31,A2)
C A3: Train
D B3: =COUNTIF($D$2:$D$31,A3)
Line B — COUNT only counts numbers and has no criteria argument. A frequency table needs =COUNTIF($D$2:$D$31,A2) to count how many times the category in A2 appears.
Question 2
A A2: Bus
B A3: Train
C B2: =COUNTIF($D$2:$D$31,A2)
D B3: =COUNTIF($D$2:$D$31,A2)
Line D — The formula beside Train must use A3 as its criteria. Using A2 counts Bus again, so Train's count is wrong. It should be =COUNTIF($D$2:$D$31,A3).
Question 3
A B2: =COUNTIF($D$2:$D$31,A2)
B B3: =COUNTIF($D$2:$D$31,A3)
C B4: =COUNTIF($D$2:$D$31,A4)
D B5: =SUM(B2:B3)
E C5: =IF(B5=B6,"OK","Check")
Line D — The total must add every count in the table. B2:B3 leaves out B4, so the check against the data total would fail. It should be =SUM(B2:B4).
Question 4
A B2: COUNTIF($D$2:$D$31,A2)
B B3: =COUNTIF($D$2:$D$31,A3)
C B4: =COUNTIF($D$2:$D$31,A4)
Line A — A formula must start with =. Without it the spreadsheet treats the entry as text. It should be =COUNTIF($D$2:$D$31,A2).
Question 5
A B2: =COUNTIF($D$2:$D$31,A2)
B B3: =COUNTIF($D$2:$D$31,A3)
C B4: =COUNTIF($D$4:$D$31,A4)
Line C — The data range must be the same in every formula and cover all the data. $D$4 skips rows 2 and 3, so some items are missed. It should be $D$2:$D$31.

Student with a class code? Join your class →