Count How Many Times Each Value Appears

Excel & Google Sheets

How it works

COUNTIF(A:A,A2) counts how many cells in column A contain the same value as A2. Copy it down and every row shows its own occurrence count: a 1 means the value is unique, and anything greater than 1 means it's a duplicate. Every copy of a duplicate shows the same count — three copies of the same email all show 3. To turn the number into a plain label, wrap it in IF: =IF(COUNTIF(A:A,A2)>1,"Duplicate","Unique"). The comparison ignores case, so ANA@CO.COM and ana@co.com count as the same value.

A:A

The whole column to search. Use $A$2:$A$20 to limit it to your data range.

A2

The value to count — the cell in this row. It stays relative so each row counts its own value.

When to use it

Use this before deduplicating any list you care about: invoice registers, email lists, employee IDs, order numbers. Seeing the counts first tells you how bad the problem is and which values to investigate before deleting anything.

Common mistakes

• ✕Expecting only the second occurrence to be flagged.

  ✓Every copy of a duplicate gets the same count, including the first. To flag only the repeats and keep the first occurrence clean, use an expanding range: =IF(COUNTIF($A$2:A2,A2)>1,"Duplicate",""). It counts only from the top down to the current row.

• ✕The range shifts when you copy the formula down.

  ✓If you use a limited range like A2:A20 without dollar signs, it slides to A3:A21 on the next row and the counts go wrong. Lock it: =COUNTIF($A$2:$A$20,A2).

• ✕Trailing spaces hide duplicates.

  ✓"Ben Okafor" and "Ben Okafor " count as different values, so real duplicates show a count of 1. Clean the column with TRIM first, or add a helper column of =TRIM(A2) and count that instead.