Excel COUNTIF No Duplicates: Count Unique Values

Formulas & functions · Updated November 22, 2026

The direct answer: Excel has no single “COUNTIF no duplicates” function, but you can count unique values (ignoring duplicates) by combining SUMPRODUCT with COUNTIF, or by using the UNIQUE function (Excel 365/2021) wrapped in COUNTA. For example, to count unique items in A2:A20, use =SUMPRODUCT(1/COUNTIF(A2:A20,A2:A20)) or =COUNTA(UNIQUE(A2:A20)).

Why COUNTIF Alone Doesn’t Work

COUNTIF counts cells that meet a condition, such as =COUNTIF(A2:A20,"Apple") which returns the number of times “Apple” appears. It cannot ignore duplicates because it counts every occurrence. To count unique values, you need a formula that evaluates each item once. The classic approach uses an array formula that divides 1 by the count of each item, then sums the results. This works because an item appearing twice contributes 1/2 + 1/2 = 1, so each unique item contributes exactly 1 to the total.

How to Count Unique Values with SUMPRODUCT

Assuming your data is in A2:A20 (adjust the range as needed), enter this formula in any cell:

=SUMPRODUCT(1/COUNTIF(A2:A20,A2:A20))

This formula counts the number of unique values in the range. For example, if A2:A20 contains “Apple” three times, “Banana” twice, and “Cherry” once, the formula returns 3. It works by creating an array of counts for each cell, then dividing 1 by each count, and summing. The result is the number of distinct entries.

Important: This formula works best when there are no blank cells in the range. If there are blanks, COUNTIF returns 0, and 1/0 produces a #DIV/0! error. To handle blanks, use =SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20,A2:A20&"")) which appends an empty string to force blanks to be counted as text, but it still may error if the range contains truly empty cells. A safer alternative is to use the UNIQUE function if you have Excel 365 or 2021.

Using UNIQUE for a Simpler Count

If you have Excel for Microsoft 365 or Excel 2021, the UNIQUE function makes this much easier. To count unique values, use:

=COUNTA(UNIQUE(A2:A20))

This returns the number of distinct values in A2:A20, ignoring blanks (COUNTA does not count empty cells). For example, with the same data, it returns 3. If you want to count unique values that appear only once (i.e., values with no duplicates), use =SUM(--(COUNTIF(A2:A20,A2:A20)=1)) which counts cells that appear exactly once. That formula works in all versions as an array formula (press Ctrl+Shift+Enter in older Excel).

How to Count Distinct Values with a PivotTable

If you prefer a non-formula approach, a PivotTable can count unique values. Select your data, go to Insert > PivotTable, and place the field in the Rows area. Then drag the same field into the Values area. By default, it shows the count of all occurrences. To change it to distinct count, right-click the value field, select Value Field Settings, and choose “Distinct Count” (available in Excel 2013 and later). This option appears only if the field is placed in the Values area and the data is in the PivotTable’s data model (you may need to check “Add this data to the Data Model” when creating the PivotTable). This method is useful for large datasets because it avoids array formulas.

Common Problems and Solutions

Several issues can trip up these formulas:

  • #DIV/0! errors: Caused by blank cells in the range. Use the UNIQUE method or filter blanks first.
  • Case sensitivity: COUNTIF is not case-sensitive, so “apple” and “APPLE” are treated as the same. If you need case-sensitive counting, use =SUMPRODUCT(--(FREQUENCY(MATCH(A2:A20,A2:A20,0),ROW(A2:A20)-ROW(A2)+1)>0)) which is an array formula.
  • Trailing spaces: Extra spaces can make “Apple ” and “Apple” appear different. Use TRIM to clean data first.
  • Numbers vs. text: Numbers stored as text may be counted separately. Use the VALUE function or clean data with Text to Columns.

Comparing COUNTIF, SUMPRODUCT, and UNIQUE

The table below summarizes the main methods:

  • COUNTIF alone: Counts occurrences, not unique. Use when you need the frequency of a specific value.
  • SUMPRODUCT(1/COUNTIF): Works in all Excel versions, but can be slow on large datasets and errors with blanks.
  • UNIQUE + COUNTA: Fast, dynamic, and handles blanks, but requires Excel 365 or 2021.
  • PivotTable Distinct Count: No formulas, but requires a few clicks and works in Excel 2013+.

For most users, UNIQUE is the best choice if available. If you’re using an older version, SUMPRODUCT is the standard workaround.

Frequently Asked Questions

How do I count unique values in Excel with multiple criteria? Use =SUMPRODUCT((range1="x")*(range2="y")/COUNTIFS(range3,range3&"")) but this gets complex. A simpler approach is to add a helper column that combines criteria, then use UNIQUE on that column. For example, if you have categories in A and items in B, create C with =A2&"|"&B2, then count unique with =COUNTA(UNIQUE(C2:C20)).

Can COUNTIF ignore duplicates in a range? No, COUNTIF does not have an option to ignore duplicates. You must use a different function or formula as described above.

Why does my SUMPRODUCT formula return #DIV/0!? This happens when there is a blank cell in the range. COUNTIF returns 0 for blanks, and 1/0 is undefined. Use the UNIQUE method or filter out blanks first, or use =SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20,A2:A20&"")) as a workaround, but it may still error if there are truly empty cells.

How do I count values that appear only once (no duplicates at all)? Use =SUM(--(COUNTIF(A2:A20,A2:A20)=1)) and press Ctrl+Shift+Enter in older Excel. This counts cells whose count equals 1. In Excel 365, you can use =ROWS(FILTER(A2:A20,COUNTIF(A2:A20,A2:A20)=1)).

Is there a way to count unique values in Excel for Mac? Yes, all the formulas work the same in Excel for Mac. The only difference is keyboard shortcuts: for array formulas, press Cmd+Shift+Enter instead of Ctrl+Shift+Enter. The UNIQUE function is available in Excel for Microsoft 365 for Mac as well.

Alternatives for Large Data Sets

If your data has thousands of rows, array formulas like SUMPRODUCT can become slow. Consider these alternatives:

  • Power Query: Go to Data > From Table/Range, then in the Power Query editor, use the “Remove Duplicates” feature and count the rows. This is efficient and doesn’t affect your original data.
  • Dynamic Array Formulas: In Excel 365, =COUNTA(UNIQUE(A2:A20000)) is fast and recalculates automatically.
  • PivotTable: As mentioned, the Distinct Count in a PivotTable is optimized for large data.

These methods avoid the computational overhead of array formulas and are more reliable for big datasets.

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.