Excel COUNTIF find duplicate values: quick answer
To use Excel COUNTIF find duplicate values in one imported order-ID list, enter =COUNTIF(A2:A7,A2) in B2. The result is 2, so ORD-104 occurs twice in A2:A7. A result greater than 1 means that the order ID occurs more than once in the imported list.
This approach does not delete or hide records. It gives each row an occurrence count so repeated IDs can be inspected before the import is reconciled against a master list.
Count each order ID in an imported list
Enter these literal order IDs in A2:A7: ORD-104, ORD-105, ORD-104, ORD-106, ORD-107, and ORD-105. This small imported-list example keeps the source IDs visible while you calculate an occurrence count before reconciling records.
| Cell | Order ID | Formula in column B | Occurrence count |
|---|---|---|---|
| A2 | ORD-104 | =COUNTIF(A2:A7,A2) | 2 |
| A3 | ORD-105 | =COUNTIF(A2:A7,A3) | 2 |
| A4 | ORD-104 | =COUNTIF(A2:A7,A4) | 2 |
| A5 | ORD-106 | =COUNTIF(A2:A7,A5) | 1 |
| A6 | ORD-107 | =COUNTIF(A2:A7,A6) | 1 |
| A7 | ORD-105 | =COUNTIF(A2:A7,A7) | 2 |
Enter the formulas
In B2, enter =COUNTIF(A2:A7,A2) and press Enter. For the remaining rows, use the same full list A2:A7 and the ID on that row as the criterion: =COUNTIF(A2:A7,A3) in B3 through =COUNTIF(A2:A7,A7) in B7. Filter the resulting count column for values greater than 1 to review repeats.
Read the result
The results in B2:B7 are 2, 2, 2, 1, 1, and 2. ORD-104 and ORD-105 require review because their counts are greater than 1; ORD-106 and ORD-107 appear once.
Keep both repeated rows visible while investigating. A repeated ID can be a legitimate repeat in the source system or an accidental duplicate export.
How the formula works
COUNTIF has two arguments: the range to inspect and the criterion to count. In =COUNTIF(A2:A7,A2), A2:A7 is the full incoming order-ID list and A2 is the ID to find in that list.
Microsoft documents the syntax as COUNTIF(range, criteria) and describes using it to count the number of times a value appears in a range. See the Microsoft COUNTIF function guidance for syntax and examples.
Boundary example: case-only order IDs
COUNTIF is not case-sensitive: ORD-104 and ord-104 are counted as the same value. It counts occurrences in this one imported column; a count of 1 does not prove that an ID is valid or present in a master list.
Case-only IDs are a boundary condition: if A2 contains ord-104 and A3 contains ORD-104, =COUNTIF(A2:A3,A2) returns 2. Use a case-sensitive comparison before treating those records as duplicates when capitalization carries meaning.
For a targeted case-sensitive check between two values, see this case-sensitive Excel comparison. Apply that check before merging or removing records if capitalization identifies distinct records.
Common duplicate-counting mistakes
Hidden spaces in imported IDs
Leading or trailing spaces can cause IDs that look identical to count separately. Clean imported IDs before using the count as a reconciliation control.
Treating a count of 1 as validation
A count of 1 means only that the ID occurs once in the selected imported list. Reconcile the reviewed list against the master list separately to establish whether that ID is valid. This guide to reconciling two Excel lists covers the next comparison step.
Deleting records before review
Removing duplicates is permanent for the later identical entries. Review the COUNTIF results or use conditional formatting first, and preserve a copy of the imported data before removing records. Microsoft distinguishes temporarily filtering or highlighting duplicates from permanently removing them in its guidance on filtering or removing duplicate values.
Compatibility and a practical fallback
COUNTIF is available in Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016, including the listed Mac versions. The same formula works in Excel for the web when the imported list is in the workbook. For a visual-only fallback, select the ID range and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
Use the visual fallback when a count column is unnecessary. Retain the COUNTIF count column when reviewers need to sort, filter, or document the number of occurrences for every row.
Review repeats before reconciliation
Once every ID with a count above 1 has been explained, corrected, or set aside, compare the reviewed list with the master list. Keeping the occurrence count until reconciliation is complete makes it easier to trace an unexpected match back to the original import.
Prepared with automated drafting and review, official references, and a limited example calculator. Synthetic examples are provided for practice; no independent human review or Microsoft Excel execution is claimed.