Flagging duplicates with a COUNTIF formula

Tip: Choose Save as PDF in the print dialog to download. Use A4 paper size.

Flagging duplicates with a COUNTIF formula

Name:
Date:
Class:
A

Quick Questions

Shade in the A or B circle for each question.

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
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
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
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
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")
B

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left columnRight column
1=COUNTIF($A$2:$A$100,A2)>1 AShows the word Duplicate for repeated values and OK for values that appear only once
2=IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","OK") BThe 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.
3A helper column holds a formula for every row that flags whether that row's value is repeated CThe test is >0, but every value counts itself at least once, so every row gives TRUE. It must be >1 to find repeats.
4The range is locked with $ so it stays the same when the formula is copied down, while the A2 part changes for each row DGives TRUE when the value in A2 appears more than once in column A, so it is a duplicate
5=COUNTIF(A2:A100,A2)>1 E=COUNTIF($A$2:$A$100,A2)>1
6=COUNTIF($A$2:$A$100,A2)>0 F$A$2:$A$100
C

Definitions

=COUNTIF($A$2:$A$100,A2)>1
D

Fill in the Blanks

Complete each line of code by filling in the missing parts.

1.
=_______($A$2:$A$100,A2)>1
2.
=COUNTIF($A$2:$A$100,A2)__
3.
=COUNTIF($A$2:$A$100,__)>1
4.
=__(COUNTIF($A$2:$A$100,A2)>1,"Dup","OK")
5.
=COUNTIF(___________,A2)>1
E

Spot the Error

Shade in the letter of the line that contains an error.

Question 1
A B1: Duplicate?
B B2: =COUNTIF($A$2:$A$20,A2)>1
C B3: =COUNTIF($A$2:$A$20,A2)>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
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
Question 4
A B1: Duplicate?
B B2: =COUNTIF($A$2:$A$20,A2)>1
C C1: Status
D C2: =IF(B2,"OK","Duplicate")
Question 5
A B2: =COUNTIF($A$2:$A$20,A2)=1
B C2: =IF(B2,"Duplicate","OK")
C Filter column C for Duplicate

Answer Key

Flagging duplicates with a COUNTIF formula
Section A: Quick Questions
Q1
A — =COUNTIF($A$2:$A$100,A2)>1
Q2
B — In a helper column next to the data
Q3
A — So the range stays the same when the formula is copied down
Q4
B — FALSE
Q5
A — =IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","OK")
Section B: Match the Pairs
Q1
=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
Q2
=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
Q3
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
Q4
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
Q5
=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.
Q6
=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.
Section C: Definitions
Q1
=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.
Section D: Fill in the Blanks
Q1
=COUNTIF($A$2:$A$100,A2)>1
Q2
=COUNTIF($A$2:$A$100,A2)>1
Q3
=COUNTIF($A$2:$A$100,A2)>1
Q4
=IF(COUNTIF($A$2:$A$100,A2)>1,"Dup","OK")
Q5
=COUNTIF($A$2:$A$100,A2)>1
Section E: Spot the Error
Q1
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.
Q2
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.
Q3
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.
Q4
D — The labels are swapped. When B2 is TRUE the row is a duplicate, so the formula should be =IF(B2,"Duplicate","OK").
Q5
A — =1 is TRUE for values that appear only once, so it flags the unique rows. To flag duplicates the test must be >1.