Finding duplicates with COUNTIF

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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)

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

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

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)

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

Match the Pairs

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

Left column		Right column
1=COUNTIF(A$2:A$100,A2)		AThe 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.
2=COUNTIF(A$2:A$100,A2)>1		B=COUNTIF(A$2:A$100,A2)
3Fix the row number in the range so every copied formula still searches the whole column		CReturns TRUE when the value in A2 appears more than once, which flags it as a duplicate
4A duplicate check formula compares each value (A2) against the whole list and counts matches		DThe 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.
5=COUNTIF(A2,A2)		EA$2:A$100
6=COUNTIF(A2:A100,A2)		FCounts how many times the value in A2 appears in the whole column range A2 to A100

Definitions

=COUNTIF(A$2:A$100,A2)

Fill in the Blanks

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

=_______(A$2:A$100,A2)

=COUNTIF(A$2:A$100,__)

=COUNTIF(A_2:A$100,A2)

=IF(B2>_,"Duplicate","Unique")

=COUNTIF(A$2:A$100,A2)_1

Spot the Error

Shade in the letter of the line that contains an 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")

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)

Question 3

A B2: =COUNTIF(A$2:A$100,B2)

B C2: =IF(B2>1,"Dup","OK")

C // Review before deleting

Question 4

A B1: Times

B B2: =COUNTIF(A$2:A$100)

C C2: =IF(B2>1,"Dup","OK")

Question 5

A C1: Status

B C2: =IF(B2<1,"Dup","OK")

C C3: =IF(B3>1,"Dup","OK")

Answer Key

Finding duplicates with COUNTIF

Section A: Quick Questions

B — =COUNTIF(A$2:A$100,A2)

A — The value appears only once, so it is not a duplicate

B — The value appears 3 times in the column

A — A$2:A$100 (absolute rows)

A — Review the matching rows before deleting any

Section B: 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.

Section C: Definitions

=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.

Section D: Fill in the Blanks

=COUNTIF(A$2:A$100,A2)

=IF(B2>1,"Duplicate","Unique")

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

Section E: Spot the Error

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).

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).

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).

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).

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.