← ACARA Digital Technologies Years 7–8

Counting data problems with a quality summary

A quality summary counts problems with formulas like this one. It counts cells flagged Check. COUNTBLANK counts empty cells. Each problem count should reach 0 after cleaning.

Part of: ACARA Digital Technologies Years 7–8 — Acquiring and Storing Data — Module 72

Understanding Counting data problems with a quality summary in ACARA Digital Technologies Years 7–8

What it does: COUNTIF counts cells that match the word Duplicate, giving the number of duplicate problems still to fix. After cleaning it should reach 0. A problem count measures what is still wrong. When the cleaning has worked, each count falls to 0. Anything above 0 means some problems remain. The data is only clean when the count reaches 0. COUNTBLANK counts empty cells in a range, so it measures missing values in the quality summary.

Common mistakes: COUNT only counts cells that hold numbers, so it can't count missing values. Use =COUNTBLANK(B2:B50) to count the empty cells. The number of rows removed is the row count before cleaning minus the row count after. It should be =48-45, which gives 3.

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 Counting data problems with a quality summary to a class →

This module's key terms are also in a printable crossword (Modules 261–300) and word search (Modules 261–324).

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 D2:D100 are flagged with the word Check?
=COUNT(D2:D100,"Check")=COUNTIF(D2:D100,"Check")
Matches
=COUNTBLANK(D2:D100,"Check")
↓
COUNTBLANK only takes a range and does not accept a second argument. To count cells flagged Check, use =COUNTIF(D2:D100,"Check") instead.
Applying
Meanings
Term
=COUNTIF(D2:D100,"Check")
Meaning
A quality summary counts problems with formulas like this one. It counts cells flagged Check. COUNTBLANK counts empty cells. Each problem count should reach 0 after cleaning.
Practice
=COUNTBLANK(B2:B9)+COUNTIF(B2:B9,">100")+COUNTIF(B2:B9,"<0")
Testing
Gaps
=(B2:B100)
Check Answer
Spots
1Quality summary
2Blank: =COUNT(B2:B50)

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

The 'Dumbo Octopus' badge — Module Maker, 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 D2:D100 are flagged with the word Check?
A. =COUNTIF(D2:D100,"Check") ✓
B. =COUNT(D2:D100,"Check")
COUNTIF counts cells that match a condition. COUNT only counts cells that hold numbers, so it can't count the word Check.
2. Which formula counts the empty cells in B2:B100 for a quality summary?
A. =COUNTBLANK(B2:B100) ✓
B. =COUNT(B2:B100)
COUNTBLANK counts empty cells. COUNT counts the cells that contain numbers, which is the opposite job.
3. After cleaning, what should each problem count in the quality summary show?
A. The same number as before cleaning
B. 0 ✓
A problem count measures what is still wrong. When the cleaning has worked, each count falls to 0.
4. Your summary says =COUNTBLANK(C2:C100) gives 12 before cleaning and 3 after. What does this tell you?
A. The data is fully clean
B. 3 missing values still need to be fixed ✓
Anything above 0 means some problems remain. The data is only clean when the count reaches 0.
5. Column D flags duplicates with the word Check. Which summary formula counts the duplicate rows still flagged?
A. =COUNTIF(D2:D100,"Check") ✓
B. =COUNTBLANK(D2:D100)
The flags are text in the cells, so COUNTIF with "Check" counts them. COUNTBLANK only counts cells with nothing in them.

Match the Pairs

=COUNTIF(D2:D100,"Check") → Counts how many cells in the flag column D say Check, so you know how many problem rows are left
=COUNTBLANK(B2:B100) → Counts how many cells in column B are empty, which shows how many missing values remain
A quality summary cell that counts how many duplicate flags are left in the flag column → =COUNTIF(D2:D100,"Duplicate")
Compare row counts before and after cleaning by counting the numbers in the cleaned column → =COUNT(B2:B100)
=COUNTBLANK(D2:D100,"Check") → COUNTBLANK only takes a range and does not accept a second argument. To count cells flagged Check, use =COUNTIF(D2:D100,"Check") instead.
=COUNTIF(D2:D100,Check) → The word Check is missing quotation marks, so the spreadsheet treats it as a name and shows an error. It should be "Check" in quotes.

Key Term

=COUNTIF(D2:D100,"Check")
A quality summary counts problems with formulas like this one. It counts cells flagged Check. COUNTBLANK counts empty cells. Each problem count should reach 0 after cleaning.

Fill in the Blanks

1. =COUNTBLANK(B2:B100)
Hint: Type the function that counts empty cells in a range, used to find missing values.
2. =COUNTIF(D2:D100,"Check")
Hint: Type the function that counts only the cells matching a flag, such as the Check flag.
3. =COUNT(C2:C100)
Hint: Type the function that counts how many cells in the range hold numbers.
4. =COUNT(A2:A100)-COUNT(F2:F100)
Hint: Type the operator that finds how many rows were removed by taking the after count away from the before count.
5. =IF(COUNTIF(D2:D100,"Check")=0,"OK","Fix")
Hint: Type the number of problems that should be left once the data is fully cleaned.

Spot the Error

Question 1
A Quality summary
B Blank: =COUNT(B2:B50)
C Bad: =COUNTIF(D2:D50,"Check")
Line B — COUNT only counts cells that hold numbers, so it can't count missing values. Use =COUNTBLANK(B2:B50) to count the empty cells.
Question 2
A Before: 48 rows
B After: 45 rows
C Removed: =48+45
Line C — The number of rows removed is the row count before cleaning minus the row count after. It should be =48-45, which gives 3.
Question 3
A Dups: =COUNTIF(E2:E50,Dup)
B Blank: =COUNTBLANK(B2:B50)
C Bad: =COUNTIF(D2:D50,"Check")
Line A — Text criteria in COUNTIF must be inside quotes. It should be =COUNTIF(E2:E50,"Dup") so it counts the cells flagged Dup.
Question 4
A Quality summary
B Blank: =COUNTBLANK(B2:B50)
C Dups: =COUNTIF(E2:E50,"Dup")
D Bad: COUNTIF(D2:D50,"Check")
Line D — A formula must start with =. Without it the cell just shows text instead of the count. It should be =COUNTIF(D2:D50,"Check").
Question 5
A Check count before: 7
B Cleaned the flagged rows
C Check count after: 7
Line C — After cleaning, the count of cells flagged Check should reach 0. If it still shows 7, the problems have not been fixed.

Student with a class code? Join your class →