Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to count duplicate values in Excel and Google Sheets
Excel & Google Sheets
=COUNTIF(A2:A6,A2)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Name |
| Alice |
| Bob |
| Alice |
| Carol |
| Bob |
=COUNTIF(A2:A6,A2)→2
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Place your list of values in column A (A2:A6).
- 2In B2 type =COUNTIF($A$2:$A$6,A2) and press Enter. This counts how many times the value in A2 appears in the full list.
- 3Copy B2 down to B3:B6. Cells showing 2 or more indicate duplicates; cells showing 1 are unique.
Tips
- To count the total number of duplicate entries (all instances of repeated values): =SUMPRODUCT((COUNTIF(A2:A6,A2:A6)>1)*1).
- To count only the number of distinct duplicated values (not the total instances): =SUMPRODUCT((COUNTIF(A2:A6,A2:A6)>1)/COUNTIF(A2:A6,A2:A6)).
- Use Conditional Formatting (Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values) to visually flag duplicates without formulas.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
How do I count duplicates in two separate columns?
Use COUNTIFS: =COUNTIFS(A2:A6,A2,B2:B6,B2) counts rows where both A and B match the current row's values.
Does COUNTIF for duplicates work with numbers?
Yes — COUNTIF works with numbers, text, and dates.