← ACARA Digital Technologies Years 7–8

Flagging duplicates with a COUNTIF formula

Gives TRUE when the value in A2 appears more than once in column A. Put it in a helper column to flag duplicate rows, then filter for TRUE.

Part of: ACARA Digital Technologies Years 7–8 — Acquiring and Storing Data — Module 58, ACARA Digital Technologies Years 9–10 — Analysing and Visualising Data — Module 6, QCE Digital Solutions Unit 4 — Complex Digital Data Exchange Problems and Solution Requirements — Module 70

Understanding Flagging duplicates with a COUNTIF formula in ACARA Digital Technologies Years 7–8

What it does: The formula goes in a new column next to the data and is copied down. Each row then shows TRUE or FALSE for its own value in column A. Absolute references with $ keep the list being searched fixed. Only the A2 part moves down to A3, A4 and so on, so every row is checked against the whole column. COUNTIF counts how many times A2 appears in the range. If that count is greater than 1, the value is a duplicate, so the result is TRUE. Filtering the helper column hides the OK rows and shows only the flagged rows, so you can check them before removing anything.

Common mistakes: Row 3 must check the value in A3, not A2. The second argument of COUNTIF should be the cell on the same row as the formula, so it should end with A3)>1. Every value counts itself, so the count is always at least 1 and >0 is always TRUE. To flag duplicates the test must be >1.

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 Flagging duplicates with a COUNTIF formula 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 gives TRUE when the value in A2 appears more than once in column A?
=COUNTIF($A$2:$A$100,A2)>1=COUNTIF($A$2:$A$100,A2)=0
Matches
=COUNTIF(A2:A100,A2)>1
↓
The range is not locked with $, so when the formula is copied down the range slides and misses the rows above. Use $A$2:$A$100.
Applying
Meanings
Term
=COUNTIF($A$2:$A$100,A2)>1
Meaning
Gives TRUE when the value in A2 appears more than once in column A. Put it in a helper column to flag duplicate rows, then filter for TRUE.
Practice
=IF(COUNTIF($B$2:$B$9,B2)>1,"Duplicate","OK")
Testing
Gaps
=($A$2:$A$100,A2)>1
Check Answer
Spots
2B2: =COUNTIF($A$2:$A$20,A2)>1
3B3: =COUNTIF($A$2:$A$20,A2)>1

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

The 'Woolly Mammoth' badge — Tundra 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 gives TRUE when the value in A2 appears more than once in column A?
A. =COUNTIF($A$2:$A$100,A2)>1 ✓
B. =COUNTIF($A$2:$A$100,A2)=0
COUNTIF counts how many times A2 appears in the range. If that count is greater than 1, the value is a duplicate, so the result is TRUE.
2. Where is the best place to put the duplicate-flagging formula?
A. Typed over the original data in column A
B. In a helper column next to the data ✓
A helper column keeps your original data safe. The formula sits beside it and flags each row without changing the values.
3. Why does the range use $A$2:$A$100 with dollar signs?
A. So the range stays the same when the formula is copied down ✓
B. So the formula only works on row 2
Absolute references lock the range. Every row then checks the same full list, while A2 changes to A3, A4 and so on.
4. A name appears once in the list. What does =COUNTIF($A$2:$A$100,A2)>1 give for that row?
A. TRUE
B. FALSE ✓
The name is counted once, and 1 is not greater than 1. The result is FALSE, so the row is not a duplicate.
5. Which formula shows the word Duplicate for repeated values and OK for the rest?
A. =IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","OK") ✓
B. =IF(COUNTIF($A$2:$A$100,A2)>1,"OK","Duplicate")
IF shows its first result when the test is TRUE. A count above 1 means a duplicate, so Duplicate must come first.

Match the Pairs

=COUNTIF($A$2:$A$100,A2)>1 → Gives TRUE when the value in A2 appears more than once in column A, so it is a duplicate
=IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","OK") → Shows the word Duplicate for repeated values and OK for values that appear only once
A helper column holds a formula for every row that flags whether that row's value is repeated → =COUNTIF($A$2:$A$100,A2)>1
The range is locked with $ so it stays the same when the formula is copied down, while the A2 part changes for each row → $A$2:$A$100
=COUNTIF(A2:A100,A2)>1 → The range is not locked with $, so when the formula is copied down the range slides and misses the rows above. Use $A$2:$A$100.
=COUNTIF($A$2:$A$100,A2)>0 → The test is >0, but every value counts itself at least once, so every row gives TRUE. It must be >1 to find repeats.

Key Term

=COUNTIF($A$2:$A$100,A2)>1
Gives TRUE when the value in A2 appears more than once in column A. Put it in a helper column to flag duplicate rows, then filter for TRUE.

Fill in the Blanks

1. =COUNTIF($A$2:$A$100,A2)>1
Hint: Type the function that counts how many cells in a range match a value
2. =COUNTIF($A$2:$A$100,A2)>1
Hint: Type the comparison that is TRUE when a value appears more than once
3. =COUNTIF($A$2:$A$100,A2)>1
Hint: Type the cell reference for the value on this row that is being checked
4. =IF(COUNTIF($A$2:$A$100,A2)>1,"Dup","OK")
Hint: Type the function that turns the test into one of two labels
5. =COUNTIF($A$2:$A$100,A2)>1
Hint: Type the whole column range, locked with dollar signs so it stays fixed when copied down

Spot the Error

Question 1
A B1: Duplicate?
B B2: =COUNTIF($A$2:$A$20,A2)>1
C B3: =COUNTIF($A$2:$A$20,A2)>1
Line C — Row 3 must check the value in A3, not A2. The second argument of COUNTIF should be the cell on the same row as the formula, so it should end with A3)>1.
Question 2
A B1: Duplicate?
B B2: =COUNTIF($A$2:$A$20,A2)>1
C B3: =COUNTIF($A$2:$A$20,A3)>1
D B4: =COUNTIF($A$2:$A$20,A4)>0
Line D — Every value counts itself, so the count is always at least 1 and >0 is always TRUE. To flag duplicates the test must be >1.
Question 3
A B2: =COUNTIF($A$2:$A$20,A2)>1
B B3: =COUNTIF($A$2:$A$20,A3)>1
C B3: =COUNT($A$2:$A$20,A3)>1
Line C — COUNT only counts numbers and does not test a value. COUNTIF counts how many cells in the range match A3, so the formula should be =COUNTIF($A$2:$A$20,A3)>1.
Question 4
A B1: Duplicate?
B B2: =COUNTIF($A$2:$A$20,A2)>1
C C1: Status
D C2: =IF(B2,"OK","Duplicate")
Line D — The labels are swapped. When B2 is TRUE the row is a duplicate, so the formula should be =IF(B2,"Duplicate","OK").
Question 5
A B2: =COUNTIF($A$2:$A$20,A2)=1
B C2: =IF(B2,"Duplicate","OK")
C Filter column C for Duplicate
Line A — =1 is TRUE for values that appear only once, so it flags the unique rows. To flag duplicates the test must be >1.

Student with a class code? Join your class →