COUNTIF in Excel: flag repeated IDs before changing the data

Decide what a repeated ID means

A small workshop exports seven booking rows. Enter BK101, BK205, BK101, BK310, an empty cell, BK101 and BK410 in A2:A8. BK101 appears three times. The other nonempty IDs each appear once.

A repeated ID is a reason to inspect the source, not automatic proof of an error. One booking might legitimately have several line items. Decide whether the intended unit is a booking, an item or an imported row before deleting anything.

Count occurrences across the whole list

COUNTIF checks the complete ID range against the value in A2. The result is 3 because BK101 occurs in A2, A4 and A7. The dollar signs keep the source range fixed when the formula is copied down.

For the row containing BK205, the equivalent formula uses A3 as the criterion and returns 1. The source range must remain A2:A8; otherwise later rows would be compared with a moving window.

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

Make a review flag and leave missing IDs visible

The formula below first checks whether the row has an empty ID. If so, it returns empty text. Otherwise, an occurrence count greater than 1 returns Review, and a count of 1 returns Single.

Enter this in B2 and copy it down in a worksheet. It flags all three BK101 rows, including the first one. It does not select which row to keep or decide whether a booking should exist only once. Missing IDs need their own completeness check; leaving the flag empty does not validate them.

=IF(A2="","",IF(COUNTIF($A$2:$A$8,A2)>1,"Review","Single"))

Three repeated rows are not three extra copies

For BK101 there are three occurrences, but only two occurrences beyond the first. If this export is supposed to contain exactly one row per booking, two rows would need investigation as possible extras.

Adding occurrence counts down the flag column would not give the number of extra rows. Each BK101 row reports 3, so those three counts alone would sum to 9. Keep the question precise: occurrence count, affected row count and distinct repeated IDs are different measures.

Check capitalization, spaces and special characters

COUNTIF text comparisons are not case-sensitive. Changing A2 to bk101 still produces a count of 3 in this example. If your identifier rules distinguish uppercase from lowercase, this formula is not a suitable exact-identity test.

The example IDs contain only letters and digits. COUNTIF criteria can interpret * and ? as wildcards, so the simple cell-reference formula is not a general literal matcher for IDs containing those symbols. Extra spaces can also split values that appear identical. Inspect and normalize data according to your ID rules before relying on the flags.

Change the repeated rows and watch the result

Change A7 to BK999: the count for A2 drops to 2. Change A4 to BK998 as well: it drops to 1, so the review formula returns Single. Clear A2 and the review formula returns empty text.

Reset restores the original three occurrences. The practice panel lets you edit the formula and source IDs, but it does not copy formulas down or remove rows. In the editor, inspect the affected records alongside their other fields before making changes.

Further reading

• Microsoft: COUNTIF behaviour and matching rules
• Microsoft: find and review duplicate values