Conditional Logic

Excel formula for Count how many times a value repeats

Show how many times each value appears in a column.

Copy this formula

=COUNTIF(A:A, A2)

How the formula works

COUNTIF compares the cell against the whole column and returns its frequency. Pair it with the duplicate flag to both detect and quantify repeats.

=COUNTIF(A:A, A2)→3

This value appears three times.

=COUNTIF(B2:B100, "Apple")→4

Apple appears four times in B2:B100.

=IF(COUNTIF(A:A, A2)>1, "Duplicate", "")→Duplicate

Flags every value that appears more than once.

How do I count repeats across two columns?

Use COUNTIFS with both columns as criteria pairs.

Is COUNTIF case-sensitive?

No. Lowercase and uppercase spellings are counted together. For a case-sensitive count use =SUMPRODUCT(--EXACT(A2:A100, "Apple")).

How do I number each repeat (1st, 2nd, 3rd occurrence)?

Use a range that grows as you fill down: =COUNTIF($A$2:A2, A2). It returns 1 for the first occurrence, 2 for the second, and so on.

How do I find the value that repeats most often?

Use =MODE.SNGL(A2:A100) for numbers. For text, use =INDEX(A2:A100, MODE.SNGL(MATCH(A2:A100, A2:A100, 0))).

Need a formula for your own data?

Describe what you want in plain language and let ExcelGPT write, explain, and verify the formula — free to start, no plugin.