← ACARA Digital Technologies Years 9–10

Finding duplicates with COUNTIF

Counts how many times the value in A2 appears in the whole column range. A result above 1 flags a duplicate, so review those rows before deleting any.

Part of: ACARA Digital Technologies Years 9–10 — Acquiring and Validating Data — Module 100

Understanding Finding duplicates with COUNTIF in ACARA Digital Technologies Years 9–10

What it does: The $ before the row numbers makes them absolute, so the range stays A2:A100 when the formula is filled down. Only the criteria cell (A2) changes. The first argument is the range to search and the second is the value to look for. So the range comes first, then A2. A result of 1 means the value matches only itself. Duplicates give 2 or more. COUNTIF returns how many cells match. A result above 1 flags a duplicate. The $ locks rows 2 and 100. A relative range slides down as you fill, so lower rows would miss the top of the list.

Common mistakes: The start of the range must be locked as A$2. Written as A3 it shifts down the column and misses the rows above, so duplicates are undercounted. It should be =COUNTIF(A$2:A$100,A3). COUNT only counts numbers and does not match a value. COUNTIF is needed to count how many times A4 appears. It should be =COUNTIF(A$2:A$100,A4).

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 Finding duplicates with COUNTIF 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 how many times the value in A2 appears in the list A2:A100?
=COUNTIF(A2,A$2:A$100)=COUNTIF(A$2:A$100,A2)
Matches
=COUNTIF(A2,A2)
↓
The range is only one cell, so the result is always 1 and no duplicates are ever found. The range must cover the whole column, such as A$2:A$100.
Applying
Meanings
Term
=COUNTIF(A$2:A$100,A2)
Meaning
Counts how many times the value in A2 appears in the whole column range. A result above 1 flags a duplicate, so review those rows before deleting any.
Practice
=COUNTIF(B$2:B$9,B2)
Testing
Gaps
=(A$2:A$100,A2)
Check Answer
Spots
2B2: =COUNTIF(A$2:A$100,A2)
3B3: =COUNTIF(A3:A$100,A3)

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

The 'Angelfish' badge — Reef Perfectionist, 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 times the value in A2 appears in the list A2:A100?
A. =COUNTIF(A2,A$2:A$100)
B. =COUNTIF(A$2:A$100,A2) ✓
The first argument is the range to search and the second is the value to look for. So the range comes first, then A2.
2. A COUNTIF duplicate check returns 1 for a row. What does that mean?
A. The value appears only once, so it is not a duplicate ✓
B. The value is a duplicate
A result of 1 means the value matches only itself. Duplicates give 2 or more.
3. A COUNTIF duplicate check returns 3 for a row. What does that tell you?
A. The value appears 3 rows below the top
B. The value appears 3 times in the column ✓
COUNTIF returns how many cells match. A result above 1 flags a duplicate.
4. You fill the formula down from row 2 to row 100. Which range keeps the whole list searched in every row?
A. A$2:A$100 (absolute rows) ✓
B. A2:A100 (relative rows)
The $ locks rows 2 and 100. A relative range slides down as you fill, so lower rows would miss the top of the list.
5. Your COUNTIF flags a row with a result of 2. What should you do next?
A. Review the matching rows before deleting any ✓
B. Delete the row straight away
A repeat may be a real separate record, such as two students with the same name. Check first, then delete only true duplicates.

Match the Pairs

=COUNTIF(A$2:A$100,A2) → Counts how many times the value in A2 appears in the whole column range A2 to A100
=COUNTIF(A$2:A$100,A2)>1 → Returns TRUE when the value in A2 appears more than once, which flags it as a duplicate
Fix the row number in the range so every copied formula still searches the whole column → A$2:A$100
A duplicate check formula compares each value (A2) against the whole list and counts matches → =COUNTIF(A$2:A$100,A2)
=COUNTIF(A2,A2) → The range is only one cell, so the result is always 1 and no duplicates are ever found. The range must cover the whole column, such as A$2:A$100.
=COUNTIF(A2:A100,A2) → The range is not absolute. When filled down, it slides to A3:A101, A4:A102 and so on, so earlier rows are skipped and the counts are wrong. It should be A$2:A$100.

Key Term

=COUNTIF(A$2:A$100,A2)
Counts how many times the value in A2 appears in the whole column range. A result above 1 flags a duplicate, so review those rows before deleting any.

Fill in the Blanks

1. =COUNTIF(A$2:A$100,A2)
Hint: Type the function name that counts how many times a value appears in a range.
2. =COUNTIF(A$2:A$100,A2)
Hint: Type the cell reference holding the value you want to count in the column.
3. =COUNTIF(A$2:A$100,A2)
Hint: Type the symbol that locks the row number so the range stays fixed when copied down.
4. =IF(B2>1,"Duplicate","Unique")
Hint: Type the number that a count must go above for the value to be flagged as a duplicate.
5. =COUNTIF(A$2:A$100,A2)>1
Hint: Type the comparison operator that tests whether the count is greater than one.

Spot the Error

Question 1
A B1: Times
B B2: =COUNTIF(A$2:A$100,A2)
C B3: =COUNTIF(A3:A$100,A3)
D C2: =IF(B2>1,"Dup","OK")
Line C — The start of the range must be locked as A$2. Written as A3 it shifts down the column and misses the rows above, so duplicates are undercounted. It should be =COUNTIF(A$2:A$100,A3).
Question 2
A B1: Times
B B2: =COUNTIF(A$2:A$100,A2)
C B3: =COUNTIF(A$2:A$100,A3)
D B4: =COUNT(A$2:A$100,A4)
Line D — COUNT only counts numbers and does not match a value. COUNTIF is needed to count how many times A4 appears. It should be =COUNTIF(A$2:A$100,A4).
Question 3
A B2: =COUNTIF(A$2:A$100,B2)
B C2: =IF(B2>1,"Dup","OK")
C // Review before deleting
Line A — The criteria must be the value in column A that is being checked (A2). B2 is the formula's own cell, which makes a circular reference. It should be =COUNTIF(A$2:A$100,A2).
Question 4
A B1: Times
B B2: =COUNTIF(A$2:A$100)
C C2: =IF(B2>1,"Dup","OK")
Line B — COUNTIF needs two arguments: the range and the value to count. The criteria A2 is missing. It should be =COUNTIF(A$2:A$100,A2).
Question 5
A C1: Status
B C2: =IF(B2<1,"Dup","OK")
C C3: =IF(B3>1,"Dup","OK")
Line B — A value is a duplicate when its COUNTIF result is above 1. Using < 1 never flags anything, because every value counts at least itself. It should be B2>1.

Student with a class code? Join your class →