COUNTIF Excel: Count Duplicates, Repeats, and Matches

Formulas & functions · Updated November 22, 2026

Yes. In Excel, COUNTIF counts how many times a value appears: with items in A2:A100, enter =COUNTIF($A$2:$A$100,A2) beside the first item and copy it down. Any result greater than 1 means that value is duplicated in the range.

What COUNTIF does

COUNTIF checks a range of cells against one condition and returns the number of cells that meet it. Its syntax is =COUNTIF(range,criteria). For duplicate checks, the range is the full list you want to inspect, while the criterion is the value on the current row.

The dollar signs in $A$2:$A$100 keep the checked range fixed when you copy the formula down. The relative reference A2 changes to A3, A4, and so on. That means each row asks how often its own value appears in the same complete list. COUNTIF is available in Microsoft 365 Excel for Windows, and the same formula works in Excel for Mac and Excel for the web.

For example, suppose A2:A7 contains Ada, Ben, Ada, Cara, Ben, and Ada. The formula returns 3 for each Ada, 2 for each Ben, and 1 for Cara. A result of 1 means the value occurs once; a result above 1 means it occurs multiple times.

Count and label duplicate entries

A count is useful for checking data, but a label can make a list easier to scan. With the list in A2:A7, enter this formula in B2 and copy it through B7:

=IF(A2="","",IF(COUNTIF($A$2:$A$7,A2)>1,"Duplicate","Unique"))

For the example list, the three Ada rows and two Ben rows are labeled Duplicate; Cara is labeled Unique. The first IF leaves a blank result when the source cell is blank, rather than labeling empty cells. If you want a numeric indicator instead of words, replace the text results with 1 and 0, then sum that helper column to count the rows that belong to repeated groups.

To count all rows whose values appear more than once, use the helper counts in B2:B7. Enter =COUNTIF(B2:B7,">1") in another cell. It returns 5 for this example: three Ada entries plus two Ben entries. This is a count of duplicate rows, including the first appearance of each repeated value, not a count of extra copies only.

Tell duplicate rows apart from extra copies

People use “number of duplicates” to mean different things. If Ada appears three times, there are three rows in the repeated group, but only two additional appearances after the first Ada. Excel can report either number, so decide which one answers your question before interpreting a total.

To flag only the second and later occurrences, use a running COUNTIF range. With the list in A2:A7, enter this in B2 and copy down:

=IF(A2="","",COUNTIF($A$2:A2,A2))

The checked range grows as the formula is copied: the first row checks A2:A2, the next checks A2:A3, and so forth. Each first appearance gets 1; later appearances get 2, 3, and so on. In the sample list, three rows get a result above 1, so there are three extra copies. To count those rows, use =COUNTIF(B2:B7,">1").

A third possible question is how many different values are duplicated. In the sample, the answer is 2: Ada and Ben. With data in A2:A7, enter this formula in C2 and copy it down:

=IF(A2="","",IF(COUNTIF($A$2:A2,A2)=1,IF(COUNTIF($A$2:$A$7,A2)>1,1,0),0))

This marks only the first appearance of each value that occurs more than once. Sum C2:C7 to get the number of distinct duplicated values; the result is 2. These three measures—repeated rows, extra copies, and distinct repeated values—are not interchangeable.

Ignore blank cells and manage the range

Blank cells can complicate a duplicate check. COUNTIF with a blank cell as its criterion can count blank cells in the checked range, so a list with several empty rows may appear to have duplicates. The IF wrapper in the formulas above prevents a blank source row from being labeled or counted as a repeated item.

Choose the range carefully. A formula that checks A2:A100 will not notice a duplicate entered later in A101. If the list is likely to grow, you can convert it to an Excel table: select a cell in the data and choose Insert > Table, then confirm whether the table has headers. Formulas entered in a table column generally fill down as the table expands. Alternatively, update the fixed range when you add records.

For a quick one-time check, select the exact data range before entering a formula. Avoid including a heading such as “Name” in the checked range unless it is meant to be treated as a possible value. A heading normally appears only once, but including it makes the formula less clear and can cause confusion if copied into later calculations.

Highlight duplicates without adding a formula column

Excel can mark repeated values directly in the worksheet. Select the cells to check, such as A2:A100, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Choose a formatting style and confirm. Excel highlights each value that occurs more than once in the selected range, including its first appearance.

Conditional formatting is convenient when you only need to spot repeats visually. It does not create a count beside each item, and the fill color is not a dependable way to calculate how many repeated rows exist. Use COUNTIF when you need a result to filter, summarize, or reference in another formula.

For more control, select the range and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With the selected range beginning at A2 and the data in A2:A100, use =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1), then choose a format. This rule avoids highlighting blank cells and applies the duplicate test to each selected row. The formula’s first cell reference should match the top-left cell of the selected range.

Check duplicates using more than one column

Sometimes a value is only a duplicate when a combination of fields repeats. A surname by itself may occur often, for example, while the combination of surname and birth date may identify a repeated record. COUNTIF checks one criterion; COUNTIFS checks multiple criteria and requires all of them to match.

Suppose names are in A2:A100 and dates are in B2:B100. To identify rows where both the name and date repeat together, enter this in C2 and copy down:

=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)

A result above 1 means that exact name-and-date combination appears more than once. This does not mean either field is individually duplicated. If empty names or dates should not count as records, add a blank check around the formula, or filter out incomplete rows before interpreting the counts.

Comparisons across multiple columns depend on what defines a duplicate in your data. For an order list, it might be order number alone; for a contact list, it could be email address; for a transaction report, it might be account, date, and amount together. Pick the fields that define the same record rather than combining columns simply because they are available.

Common COUNTIF problems and what they mean

  • Unexpected matches in text: COUNTIF is not case-sensitive. It treats “River” and “RIVER” as the same for this comparison. If letter case distinguishes records, COUNTIF alone is not sufficient.
  • Values look identical but do not match: Leading or trailing spaces, nonbreaking spaces, or other invisible characters can make text values different. Check and clean the source data before deciding a formula is wrong.
  • Dates appear duplicated: Excel stores dates as serial values, and a cell can also contain a time that is hidden by its number format. Two cells may display the same date but hold different times. Display more detail or compare the underlying values before treating them as exact duplicates.
  • Wildcards affect text criteria: In COUNTIF criteria, an asterisk matches any sequence of characters and a question mark matches one character. To search for a literal asterisk or question mark, precede it with a tilde, as in ~* or ~?.
  • The result changes after copying: Confirm that the full comparison range uses dollar signs, such as $A$2:$A$100, while the row’s criterion remains relative, such as A2.

When a count seems surprising, inspect the raw values and the formula references first. Formatting changes how a value looks, but does not necessarily change what COUNTIF compares. For text imported from another system, cleaning inconsistent spaces and standardizing formats can be more important than changing the formula.

COUNTIF compared with other ways to find duplicates

Use COUNTIF when you need a count for each item or a formula-driven duplicate flag that updates when the data changes. Use conditional formatting when a visual highlight is enough. Use COUNTIFS when a duplicate is defined by matching several fields.

Excel’s Remove Duplicates command is different: it deletes repeated records rather than reporting them. On Windows, find it on the Data tab in the Data Tools group. Review the selected columns and keep a copy of the source data before using it, because removed rows are not a substitute for an audit trail. This tool is useful for producing a list of distinct records, not for counting how often each original value appeared.

A PivotTable can also summarize a list by value and show a count for each group. It is helpful when you want a compact summary of many categories, while COUNTIF is often simpler for a small list or a count displayed beside every row. For a dynamic distinct list, UNIQUE is available in Microsoft 365 and Excel 2021 or later, but it returns unique values rather than the frequency of each value; pair it with a counting method if you need totals.

Frequently Asked Questions

Does COUNTIF count both copies of a duplicate?
Yes. If a value appears twice, COUNTIF returns 2 for either matching row when each row is compared against the full list. It does not label one copy as original and the other as duplicate.

How do I count only the second and later occurrences?
Use an expanding range such as =COUNTIF($A$2:A2,A2) in the first data row and copy down. Count results greater than 1 to count extra appearances after the first.

Can COUNTIF find duplicates across two columns?
Use COUNTIFS when both fields must match. For example, compare the current name against the name column and the current date against the date column; a result above 1 identifies a repeated combination.

Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF treats text comparisons without regard to case. Use a case-sensitive approach if capitalization is part of the identity of a value.

Why does Excel show a duplicate when a cell looks blank?
The cell may contain spaces or an invisible character, or the formula may be counting genuine blank cells. Check the underlying contents and use a blank guard such as IF(A2="","",…) when empty rows should be ignored.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.