KutoolsforOffice — One Suite. Five Tools. Get More Done.

Count distinct values in Excel, including the first occurrence of duplicates

AuthorAmanda LiLast modified

A list may contain values that appear once and others that appear several times. When you need the total number of different customers, products, departments, or other entries, each value should be counted only once, regardless of how many times it occurs.

Formula Helper Plus in Kutools for Excel includes Count distinct values in Excel (unique and 1st duplicate occurrences) for this calculation. Select the range, and Kutools creates the required SUMPRODUCT and COUNTIF formula without requiring you to write or remember it.

Kutools Formula Helper Plus

What Count distinct values can do

Count distinct values returns the number of different entries in a selected range. A value that appears once contributes one to the total, and a value that appears multiple times also contributes only one.

🔢 Count different values

Return one total showing how many different entries appear in the selected range.

1️⃣ Count each value once

Count a value once whether it appears in one cell or is repeated throughout the list.

🔄 Keep the result dynamic

Insert a formula that recalculates when values in the referenced range change.

🧮 Build the formula automatically

Create the SUMPRODUCT and COUNTIF combination by selecting only the source range.

Benefits of Count distinct values

Count distinct values simplifies a useful calculation that otherwise requires a nested array-style formula.

🧠

No formula memorization

Count distinct entries without remembering how SUMPRODUCT and COUNTIF must be combined.

🖱️

Only one range to select

Specify the source range through a single labeled argument box.

✅

Reduce syntax mistakes

Let Kutools create the reciprocal COUNTIF calculation and its required references.

📊

Return a single summary value

Get the distinct count directly without first extracting a separate list of distinct entries.

How to use Count distinct values

In this example, customer names are stored in cells A2:A21. Several customers appear in multiple orders, but the required result is the number of different customers.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Integrated with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...
  1. Select cell C2, where you want to return the distinct count.
  2. Click Kutools > Formula Helper > Formula Helper Plus.
    Open Formula Helper Plus from the Kutools Formula Helper menu
  3. In the Formula Helper Plus dialog box, switch to the Local tab, select the Count category and choose Count distinct values in Excel (unique and 1st duplicate occurrences).
  4. In the Range box, select cells A2:A21.
    Formula Helper Plus dialog box
  5. Click OK. Kutools inserts the formula into cell C2 and closes the dialog box.
    Kutools inserted the formula automatically

The generated formula follows this logic:

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

COUNTIF determines how many times each value appears in the range. Dividing 1 by those occurrence counts distributes a total contribution of one across all occurrences of each value. SUMPRODUCT then adds those contributions, so every different customer is counted once.

📝 Note:

The selected range should not contain blank cells. This formula treats all blank cells together as one distinct value, which can increase the result by one.

Tips for better results

Prepare the source range consistently so entries that should match are counted as the same value.

  1. Exclude the header: Select only the data cells, such as A2:A21, rather than including the column heading.
  2. Remove blank cells: Blank cells are counted collectively as one distinct entry by this formula.
  3. Check leading and trailing spaces: Values such as "Acme Supplies" and "Acme Supplies " may be treated as different entries because of hidden spaces.
  4. Standardize imported data: Correct inconsistent punctuation, abbreviations, and spelling before counting.
  5. Remember that counting is not case-sensitive: COUNTIF treats values such as "ACME SUPPLIES" and "Acme Supplies" as the same entry.
  6. Use a bounded range: Select the actual data range instead of an entire column to avoid including large numbers of blank cells.

Best use cases

This calculation is useful whenever repeated records must be reduced to a single count of different entries.

Distinct customers

Count how many different customers appear in an order or transaction list.

Example: Twenty orders contain eight different customer names.

Product and inventory records

Count different product codes, SKUs, brands, or inventory locations.

Example: Count the number of different SKUs included in a shipment log.

Employee and department lists

Count different employees, teams, departments, or office locations in repeated records.

Example: Count how many departments appear in a project assignment list.

Survey and event data

Count different respondents, attendees, answers, or registration codes.

Example: Count unique attendee email addresses in an event check-in list.

About Formula Helper Plus

Formula Helper Plus is a formula library in Kutools for Excel that helps you find and insert useful formulas through a guided interface. Formulas are organized into categories, and each one provides labeled argument boxes and instructions explaining the required inputs. You can also create categories and formulas for calculations you use regularly, or share useful formulas to help expand the Online formula library.

Kutools Formula Helper Plus
  • 🗂️ Category tools
    1. Category list: Browse formulas by category, such as Calculate, Compare, Count, Date, Lookup, and Text.
    2. Add category Add category button: Create a category for organizing your own formulas.
    3. Rename category Rename category button: Select a custom category, and click this button to rename it.
  • 🧮 Formula tools
    1. Search: Enter keywords in the search box to find a formula by its name or purpose.
    2. Add formula Add formula button: Create a formula and assign it to a category. When defining the formula, enclose each variable argument in # signs, such as #Range#.
    3. Edit formula Edit formula button: Select a custom formula, and click this button to modify its formula, category, or description.
    4. Copy formula Copy formula button: Create an editable copy of the selected formula. This is useful when you want to build a new formula based on an existing one.
    5. Upload formula Upload formula button: Upload and share the selected formula to help us expand the Online formula library.

Frequently Asked Questions

What does “unique and 1st duplicate occurrences” mean?

A value that appears once is counted once. When a value appears several times, only one occurrence contributes to the final count. In other words, every different value is counted once.

Does this calculation return the distinct values themselves?

No. It returns only the number of different values in the selected range. It does not create a separate list of those values.

To extract the different values into a separate range, use Kutools > Formula Helper > Text > Extract cells with unique values (include the first duplicate value).

Are uppercase and lowercase values counted separately?

No. Because the formula uses COUNTIF, text matching is not case-sensitive. For example, ACME SUPPLIES and Acme Supplies are counted as the same value.

Are blank cells counted?

Yes. One or more blank cells in the selected range collectively contribute one to the distinct count. Remove blanks or select a range without blank cells when blanks should not be included.

Why is the result larger than expected?

Check for blank cells, leading or trailing spaces, spelling differences, and inconsistent punctuation. These can cause entries that look similar to be counted separately.

Does the result update when the source data changes?

Yes. Kutools inserts a formula, so the result recalculates when values within the referenced range change.

Can the range contain both numbers and text?

Yes. The formula can count distinct numbers and text values in the same range. Make sure values are stored consistently, because a number stored as text may be treated differently from a numeric value.