COUNTIF Duplicates in Excel: Examples and Ways to Fix Them

Errors & fixes · Updated November 22, 2026

To count how often a value appears in Excel, enter =COUNTIF($A$2:$A$7,A2) beside your list and fill it down. With product names in A2:A7—Red, Blue, Red, Green, Blue, Red—the formula returns 3 beside each Red, 2 beside each Blue, and 1 beside Green.

What COUNTIF Counts as a Duplicate

COUNTIF checks a range for cells that match a criterion, then returns the number of matches. In the example above, the range $A$2:$A$7 contains the complete list, while A2 supplies the value to look for. The dollar signs keep the list range fixed when you copy the formula down; the relative reference A2 changes to A3, A4, and so on.

A count greater than 1 means the value appears more than once in the chosen range. It does not mean every counted row is a later, extra copy: the first Red is also assigned a count of 3 because COUNTIF reports the total number of matching cells. This distinction matters when deciding whether to label all members of a duplicate group or only the repeats after the first occurrence.

COUNTIF is not case-sensitive, so Red and RED are treated as the same value. It compares cell contents rather than judging whether two entries are the same real-world item. For example, a product code stored as text may not behave as expected alongside a numeric code, and a value with a trailing space may not match the same-looking value without one.

Set Up and Copy the Basic Formula

Put the list in one column and reserve the next column for the counts. For the example list in A2:A7, type a heading such as Count in B1, enter the formula below in B2, then fill it down through B7.

With values in A2:A7 and the formula entered in B2, use: =COUNTIF($A$2:$A$7,A2). For the sample values Red, Blue, Red, Green, Blue, Red, the returned counts are 3, 2, 3, 1, 2, and 3, respectively.

  1. Select B2 and enter the formula.
  2. Press Enter. Check that B2 displays 3 because Red occurs three times.
  3. Copy the formula down to B7 by dragging the fill handle, or select B2:B7 and use Ctrl+D on Windows. On Mac, use Command+D to fill down.
  4. Filter or sort the count column if you want to review repeated values together. Keep the original data intact until you have checked the results.

The fixed range is important. If you instead use A2:A7 without dollar signs, the range will shift as you fill down, and later rows may be compared against only part of the list. If you expect to add records, extend the range in advance or use an Excel Table so formulas can refer to a growing column.

Mark Only the Extra Occurrences

Sometimes the goal is not to show the total frequency but to identify rows that repeat a value already seen above. A growing range does that: its first cell stays fixed, while its bottom edge expands with each row. The first occurrence gets a count of 1; later copies get 2, 3, and so forth.

With the list in A2:A7 and the first result in B2, enter: =COUNTIF($A$2:A2,A2)>1. In the sample, the formula returns FALSE for the first Red, Blue, and Green, and TRUE for the second Red, second Blue, and third Red. The comparison >1 converts the running count into a simple TRUE/FALSE flag.

If you prefer words rather than TRUE and FALSE, use an IF formula. With values in A2:A7 and the result in B2, enter: =IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","First occurrence"). The result for A2 is First occurrence, while the later Red in A4 is Duplicate. Copy it down to classify each row in order.

Row order therefore affects this method: whichever matching value appears first is retained as the first occurrence. If you sort the source list, the flagged row can change, although the total count of each value does not. Save or record the original order if it has meaning to your workflow.

Highlight Duplicates Without Adding a Count Column

To color matching values directly, select A2:A7, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Select a formatting style and confirm. Excel highlights every value occurring more than once in the selected range, including the first occurrence. This is convenient for visual review, but it does not give you a count beside each entry.

For greater control, select the range and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With A2:A7 selected and A2 as the top cell of the selection, use: =COUNTIF($A$2:$A$7,A2)>1. Choose a format and confirm the rule. The reference A2 is relative, so Excel evaluates each selected row against its own value, while the fixed range remains A2:A7.

On Mac, Conditional Formatting is also available from the Home tab, though the rule dialog may be arranged differently from Windows. Excel for the web supports standard conditional formatting as well; the available rule controls can differ by version. If the result looks wrong, verify that the selected range begins on the same row as the relative cell reference in the formula.

Count Duplicate Combinations Across Columns

A value in one column may not be unique by itself. For example, the same employee name can appear in several departments, and a repeated invoice number may be legitimate if it belongs to a different customer. In those cases, count a combination of fields rather than counting one column alone.

Suppose names are in A2:A10 and departments are in B2:B10. With a result formula in C2, use: =COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,B2). If the pair in A2 and B2 appears in rows 2 and 8, the formula returns 2 for that pair. COUNTIFS applies both conditions to corresponding rows, so both the name and department must match.

This approach can be extended with additional range-and-criterion pairs, such as including a date or location. Each criteria range must cover the same number of rows as the other ranges. If one range starts or ends on a different row, the result can be misleading or Excel may report an error.

Handle Blanks, Spaces, and Other Data Issues

Blank cells deserve special attention. A COUNTIF formula that uses a blank cell as its criterion can count other blank cells, making empty rows appear to be duplicates. To leave the result empty when the source cell is empty, use an IF check first.

With values in A2:A7 and the formula in B2, enter: =IF(A2="","",COUNTIF($A$2:$A$7,A2)). This returns an empty display for a blank A2; for a nonblank value, it returns that value’s count in the full list. A cell containing a formula that returns an empty string may also look blank, so consider how your source data creates empty-looking cells.

Whitespace is another frequent cause of surprising counts. For instance, “Blue” and “Blue ” may look alike but can be treated as different text. To create a cleaned version, use a helper column rather than changing the original data immediately. With raw text in A2, enter in B2: =TRIM(A2). TRIM removes leading and trailing ordinary spaces and reduces repeated internal ordinary spaces to one. It does not remove every possible nonprinting character, such as some nonbreaking spaces copied from web pages.

After cleaning, count the cleaned column, not the original one. If values in B2:B7 are the cleaned list, put the count in C2 and use: =COUNTIF($B$2:$B$7,B2). Check a sample of the cleaned results before relying on them, particularly when spaces or punctuation may be meaningful in codes.

Also check the data type. Numbers stored as text can cause inconsistencies when compared with actual numbers, and dates are stored as serial values even though Excel displays them in a date format. Make sure the entries you intend to compare are consistently typed and formatted. COUNTIF does not distinguish uppercase from lowercase; if case-sensitive duplicate detection is essential, COUNTIF alone is not sufficient.

Choose the Right Duplicate-Checking Method

COUNTIF is useful when you need a transparent formula, a visible frequency beside each row, or a rule that updates as the list changes. The built-in Duplicate Values conditional-formatting rule is quicker for a visual scan. A running COUNTIF is better when you need to distinguish the first occurrence from later repeats.

  • Use a full-range COUNTIF when you want each row to show the total number of matching values.
  • Use a growing-range COUNTIF when you want to flag only occurrences after the first.
  • Use COUNTIFS when a duplicate means that several columns match as a group.
  • Use conditional formatting when color is enough and you do not need a count column.
  • Use Data > Remove Duplicates only when you intend to delete repeated records. Make a copy first, because removal changes the data and can discard meaningful rows.

For a large or regularly refreshed dataset, convert the range to a Table with Insert > Table. Tables expand as rows are added and make formulas easier to read with column names. A table formula may use structured references, but the basic COUNTIF method is often easier to understand when learning or troubleshooting. Dynamic-array functions such as UNIQUE are also available in Microsoft 365 and Excel 2021 or later for producing distinct-value lists, but they answer a different question from counting each row’s occurrences.

Common COUNTIF Mistakes

The most common error is mixing up total frequency with extra copies. A result of 3 means three occurrences in the chosen range; it does not say that the current row is the third one. Use the running-range method if you want a flag only on later copies.

Another mistake is locking the wrong part of the reference. In the full-list formula, dollar signs around both ends of the range keep the comparison list constant. In the running formula, only the first endpoint is fixed so the range can grow as the formula is copied down. Check the formula in the last row to confirm that it still covers the intended records.

COUNTIF also accepts criteria such as text, numbers, cell references, and wildcard patterns. The asterisk wildcard represents any sequence of characters and the question mark represents one character, so a criterion containing wildcards may match more than an exact duplicate. If values themselves contain wildcard characters, they may need special handling. For ordinary duplicate checks, referring to the cell containing the complete value avoids accidentally using a broad pattern.

Frequently Asked Questions

How do I count duplicates in one Excel column?
With the list in A2:A100 and a result beside it, use =COUNTIF($A$2:$A$100,A2) in the first result row and fill down. A result above 1 means that value appears multiple times in the selected list.

How can I highlight duplicates in Excel?
Select the data range and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Choose a style and confirm. This highlights all members of repeated groups, not just the later copies.

How do I flag only the second and later occurrence?
With data starting in A2, use a running range such as =COUNTIF($A$2:A2,A2)>1 in the adjacent column and fill down. The first occurrence is FALSE; later occurrences are TRUE.

Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF is case-insensitive, so Red and RED count as matches. If case must matter, use a case-sensitive method based on EXACT rather than relying on COUNTIF alone.

Why does COUNTIF show an unexpected duplicate count?
Check for hidden leading or trailing spaces, blank cells, inconsistent number-versus-text types, and an incorrectly fixed range. Also confirm that you want a total frequency rather than a flag for later occurrences; those are different calculations.

Related

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.