COUNTIF Not Duplicate in Excel: Count Unique Values

Formulas & functions · Updated November 22, 2026

To count values that appear only once in Excel, use =SUMPRODUCT((A2:A10<>"")*(COUNTIF(A2:A10,A2:A10)=1)) when the values are in A2:A10; it ignores blanks and returns the number of nonblank entries with no duplicate. If you mean the number of different values regardless of repeats, use a distinct-value formula instead: “not duplicate” can describe more than one counting task.

What “not duplicate” can mean

Excel users often use “unique” and “not duplicate” to mean different things. Before choosing a formula, decide which result you want. A list containing Red, Blue, Red, Green has three distinct values—Red, Blue, and Green—but only Blue and Green appear exactly once. Those are different counts.

  • Distinct values: Count each different entry once, even if it appears repeatedly. The example list has three.
  • Values appearing once: Count only entries whose total frequency is one. The example list has two.
  • First occurrences: Mark the first appearance of each value and ignore later repeats. The example list has three first appearances.

COUNTIF can help with all three tasks, but one COUNTIF formula does not answer all of them in the same way. The distinction matters in tasks such as counting unique customers, finding one-off product codes, or removing repeated records.

How COUNTIF works

COUNTIF counts cells in a range that meet one condition. Its syntax is COUNTIF(range, criteria). The range is the cells to inspect, and the criteria is the value or rule to match. For example, with names in A2:A10 and a name to check in C2, =COUNTIF(A2:A10,C2) returns how many times the value in C2 occurs in the range.

To check a value on each row, put the current cell in the criteria argument and keep the full list as the range. With names in A2:A10 and the row’s name in A2, =COUNTIF($A$2:$A$10,A2) returns that name’s total count. The dollar signs keep the list range fixed if you copy the formula down, while A2 changes to A3, A4, and so on.

COUNTIF is not case-sensitive: it treats “Sam” and “SAM” as the same text for an ordinary comparison. It also recognizes wildcard characters in criteria: an asterisk represents any sequence of characters, and a question mark represents one character. These details can affect what Excel considers a match, so they are worth checking when results seem surprising.

Count values that appear exactly once

For Microsoft 365 on Windows, the opening formula counts each nonblank cell only if its value occurs exactly once in the full range. With the list in A2:A10, =SUMPRODUCT((A2:A10<>"")*(COUNTIF(A2:A10,A2:A10)=1)) returns 2 for Red, Blue, Red, Green, blank, Green, Orange, blank, Yellow only if each listed one-off is considered; for that exact list it returns 4, because Blue, Orange, and Yellow are singletons and there are three, not four. The correct result is 3.

The formula combines two tests. A2:A10<>"" excludes blank cells, while the COUNTIF test checks whether each value’s total frequency equals 1. SUMPRODUCT adds the TRUE/FALSE results as 1s and 0s. In the example, Blue, Orange, and Yellow each pass both tests, so the answer is three. Red and Green fail because each occurs twice.

If you want blank cells treated as a value that may itself be duplicated, remove the nonblank test only if that is genuinely the intended meaning. Usually, empty cells are missing data rather than a category to count, which is why excluding them is safer for a “how many one-off entries?” question. Also note that a cell containing a formula that returns an empty string may look blank; the nonblank test shown will exclude it.

Count distinct values, including repeated entries once

For a distinct count in Microsoft 365 or Excel 2021 and later, UNIQUE can return one copy of each value. With data in A2:A10, =COUNTA(UNIQUE(FILTER(A2:A10,A2:A10<>""))) counts the nonblank distinct values. For the list Red, Blue, Red, Green, blank, Green, Orange, blank, Yellow, the result is 5: Red, Blue, Green, Orange, and Yellow.

FILTER removes empty cells before UNIQUE creates the distinct list, and COUNTA counts the resulting entries. If you want to see the unique values rather than just count them, enter =UNIQUE(FILTER(A2:A10,A2:A10<>"")) in an empty cell; Excel spills the results into the cells below. Make sure that spill area is clear, or Excel may show a #SPILL! error.

UNIQUE is available in Microsoft 365 and Excel 2021 or later. It is not available in every older Excel edition. If your Excel version does not support it, a PivotTable can provide a distinct list: select the data, choose Insert > PivotTable, and place the field in the Rows area. The PivotTable shows each label once. To get a count, count those displayed labels or use a suitable older-version formula approach.

Count only the first occurrence of each value

Sometimes “not duplicate” means keeping the first row for each item and identifying later repeats. A helper column is straightforward. With data in A2:A10, enter this in B2 and fill it down: =COUNTIF($A$2:A2,A2)=1. The first occurrence returns TRUE; later occurrences return FALSE. For the example list, the first Red, first Blue, first Green, first Orange, and first Yellow are TRUE, while the second Red and second Green are FALSE.

This formula uses a growing range: the first part begins at A2 and ends at the current row. That means each row is compared only with the entries above it and itself. It is not the same as testing whether a value appears exactly once in the whole list. For example, the first Red is marked TRUE even though another Red appears later. Use the full-range count formula when you need to exclude every value that has any duplicate.

To count the TRUE flags, with the helper results in B2:B10, use =COUNTIF(B2:B10,TRUE). For the example, it returns 5 because there are five different nonblank values with a first occurrence. If the source range may contain blanks, use a blank-excluding helper formula instead. With data in A2:A10, enter in B2 and fill down: =IF(A2="","",COUNTIF($A$2:A2,A2)=1). This leaves blank source rows blank rather than marking the first blank as an item.

Choose the right formula for your goal

Use the result you need to guide the choice, not the word “unique” by itself. A report of how many different products were sold usually needs a distinct count. A check for product codes that occur only once needs the exactly-once formula. A cleaned list that retains the earliest record for each customer needs a first-occurrence test or Excel’s Remove Duplicates feature.

  • How many distinct nonblank values? Use COUNTA with UNIQUE and FILTER in a supported Excel version.
  • How many entries have no match elsewhere? Use SUMPRODUCT with COUNTIF testing for a count of 1.
  • Which rows are the first copy? Use a growing-range COUNTIF helper formula.
  • Do you need to delete repeated rows? Make a copy first, then consider Data > Remove Duplicates. It changes the selected data rather than merely reporting a count.

For a reusable report, consider converting the data range to an Excel Table with Insert > Table. Tables expand as you add rows, which can make formulas and references easier to maintain. Check that the formula includes the full intended dataset, especially when new records are appended below an ordinary fixed range such as A2:A100.

Common problems and how to diagnose them

Blanks affect the result. Decide whether empty cells should count. The formulas above deliberately exclude blanks for distinct and exactly-once counts. If a column contains spaces instead of truly empty cells, those spaces are text and may be counted. You can clean data with TRIM in a helper column or inspect suspicious cells.

Numbers stored as text may not match numbers. A cell containing the number 123 and another containing text “123” can behave differently in comparisons or later analysis. Check the number format and the underlying values; changing the display format alone does not always convert text to a number. Use Excel’s conversion tools or a carefully checked helper column when the source data is inconsistent.

Wildcard characters can create unexpected matches. If the data itself contains a literal asterisk or question mark, COUNTIF may interpret it as a wildcard when used as a criterion. A tilde can escape a wildcard in a COUNTIF criterion, but constructing a criterion that safely escapes arbitrary user-entered text takes extra care. If such characters occur in your data, test a small sample before relying on the count.

Errors can disrupt a calculation. If the range contains values such as #N/A, formulas that compare or process the range may produce an error instead of a count. Identify whether those cells should be fixed, excluded, or counted as errors; do not hide the problem with IFERROR unless replacing the error with a chosen result is appropriate for the task.

Case-sensitive matching needs a different approach. Standard COUNTIF does not distinguish uppercase from lowercase. If “ab” and “AB” must count as different values, a case-sensitive method based on EXACT is more suitable, but it needs careful array handling and may be more complex than a basic COUNTIF. Confirm the required comparison rule before building a report.

COUNTIF versus Remove Duplicates

COUNTIF-based formulas calculate or label results while leaving the source list intact. That is useful when you need an audit trail, want to see every repeated row, or need a count that updates as entries change. A helper column also makes it easier to filter for TRUE or for a total count greater than one.

Remove Duplicates is a data-cleaning command, not a counting formula. In Excel for Microsoft 365 on Windows, select the relevant data and choose Data > Remove Duplicates. Excel then removes repeated rows based on the columns you select. This action changes the data, so keep a backup or copy first. It also considers entire selected rows or selected columns, which can produce a different outcome from counting duplicates in just one field.

For instance, two rows with the same customer name but different order dates may be duplicates by customer name, yet distinct records by the full row. Decide which columns define a duplicate before cleaning. A COUNTIF on the name column answers how often each name occurs; Remove Duplicates on the full table can preserve both orders if another field differs.

Frequently Asked Questions

How do I count unique values in Excel with COUNTIF?
COUNTIF alone counts occurrences for a criterion; it does not directly return a distinct count from a whole list. In Microsoft 365 or Excel 2021 and later, use UNIQUE with FILTER and COUNTA for a distinct count, or use the SUMPRODUCT and COUNTIF formula when you mean entries that appear exactly once.

What formula counts a value only if it appears once?
With values in A2:A10, use =SUMPRODUCT((A2:A10<>"")*(COUNTIF(A2:A10,A2:A10)=1)). It counts nonblank cells whose total number of matches in the range is one.

How can I flag duplicates and nonduplicates in a column?
With data in A2:A10, put =COUNTIF($A$2:$A$10,A2) in a helper column and fill down. A result of 1 means the value occurs once; a result above 1 means it is repeated. This also gives each repeated row the same total frequency.

Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF is not case-sensitive, so ordinary comparisons treat uppercase and lowercase versions of the same text as matches. Use a case-sensitive method if that distinction is important.

Why is my unique count different from my count of values appearing once?
A distinct count includes every different value one time, even if it repeats many times. An exactly-once count excludes every value that appears more than once. For Red, Blue, Red, the distinct count is 2, while the exactly-once count is 1.

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.