How to Count Duplicate Values Only Once in Excel (6 Methods)

When working with sales data, customer names, product IDs, or survey responses, it’s common to encounter repeated entries in your Excel sheets. In many scenarios, you don’t just want to count how many times a value appears but you want to count each duplicate only once, regardless of how often it shows up. For example, if “Alice” appears five times, it should still only be counted as one. This type of summary is crucial for accurate reporting, deduplication, and identifying unique participants or items within a larger dataset.

In this article, we’ll walk you through multiple effective methods to count duplicate values only once in Excel. We’ll cover everything from case-sensitive comparisons and number-only datasets to formula-based solutions and built-in filtering options. Let’s get started.

If your dataset includes entries that look similar but differ in letter casing such as “Alice”, “ALICE”, and “alice” and you want to treat each version as a separate value, this method is the ideal solution. Excel’s default functions treat text comparisons as case-insensitive, so using the EXACT function is crucial when you need precision. The EXACT function performs case-sensitive comparisons, while FREQUENCY and MATCH functions help identify and count each unique, case-specific value only once. Wrapping this logic in SUM and IFERROR functions ensures you get an accurate count without duplication errors.

This is the sample dataset we will be using:

Steps:

➤ Click on a blank cell where you want to display the result, such as C2.
➤ Type the following array formula:

➤ Press  Ctrl  +  Shift  +  Enter  to confirm, as this is an array formula.

Excel will now return the count of all duplicate values in range A2:A11 counting each case-sensitive match only once.

If you’re working with a list where entries like “Alice” and “ALICE” should be treated as the same, you can use a formula that ignores case and counts each duplicate only once. This method works for both text and numbers and is useful when you want to count unique values regardless of how many times they appear or how they’re capitalized.

This approach leverages the FREQUENCY and MATCH functions inside an array formula to count each distinct entry just once, even if they appear multiple times.

Steps:

➤ Select a blank cell where you want to display the count (e.g., C2).
➤ Enter the following array formula (press  Ctrl  +  Shift  +  Enter  after typing):

➤ Press  Ctrl  +  Shift  +  Enter  (not just Enter) to confirm as an array formula.

Excel will now return the count of unique entries in range A2:A11, treating all case variations as the same.

This method is a quick and reliable way to count distinct entries in a range, ignoring case differences. Unlike traditional array formulas, this doesn’t require pressing  Ctrl  +  Shift  +  Enter  , making it more user-friendly for most Excel users. It works on both text and numbers, efficiently counting each unique value just once.

The COUNTIF function determines how many times each item appears in the range. By dividing 1 by that count and then summing with SUMPRODUCT function, duplicates are effectively counted only once.

Steps:

➤ Select any blank cell where you want the result to appear, such as C2.
➤ Enter the following formula:

This counts all non-blank unique values in A2:A11 without case sensitivity.

➤ Press Enter for output.

You’ll now see the total number of unique entries in your dataset, regardless of how many times duplicates occur.

If your dataset contains only numeric values (no text entries), this method offers a straightforward and fast solution. The FREQUENCY function works well with numbers and is perfect for situations where you’re trying to count how many unique numbers exist, ignoring repetitions. This approach avoids complex logic and is especially useful when working with data like ID numbers, prices, or quantities.

We’ll use a dataset that contains several repeated values, such as 101 and 205, appearing multiple times. Using the numeric counting method, we will accurately count each distinct number only once, regardless of duplication.

Steps:

➤ Click on a blank cell where you want to display the result, e.g., C2.
➤ Enter this array formula (press  Ctrl  +  Shift  +  Enter  if needed):

Excel will now count how many unique numeric values are in the range A2:A11 and return 5 for the dataset above because the unique numbers are 101, 205, 330, 450, and 600.

If you have access to Excel 365 or Excel 2019, the built-in UNIQUE function provides the simplest and most efficient way to count distinct values. This dynamic array function extracts unique entries from a range automatically.

Steps:
➤ Select any blank cell where you want to display the count, such as C2.
➤ Enter the following formula:

➤ Press Enter (no need for  Ctrl  +  Shift  +  Enter  in modern Excel versions).

Excel will return the total count of unique values in the range A2:A11, counting each duplicate only once.

For users who prefer a formula-free, manual approach, Excel’s built-in Advanced Filter tool offers an easy way to extract unique values from a range. After extracting, you can simply count the unique entries using the ROWS function.

Steps:

➤ Highlight the range A2:A11 containing your data.
➤ Go to the Data tab on the Ribbon, then click Advanced in the Sort & Filter group.

➤ In the Advanced Filter dialog box, select Copy to another location.
➤ In the Copy to field, select an empty cell where you want to place the unique list (for example, E1) and tick the checkbox Unique records only.
➤ Click OK. Excel will copy the unique values starting at cell E1.

➤ To count the number of unique values extracted, enter the following formula in any blank cell:

This formula returns the count of unique entries extracted by the filter.

Frequently Asked Questions

Does Excel treat “Alice” and “ALICE” as duplicates?

By default, Excel’s functions like COUNTIF are not case-sensitive, so “Alice” and “ALICE” are considered the same. To distinguish based on letter case, you need to use functions like EXACT which perform case-sensitive comparisons.

Can I count unique numeric values using the same formulas?

Yes, many of the formulas work for numbers and text. However, using the FREQUENCY function is specifically designed for numeric data and offers a simpler, faster way to count unique numbers in a range.

What if my data has blank cells?

The formulas shown automatically ignore blank cells, so you don’t need to remove or filter them manually. They count only cells containing actual values, ensuring your unique count remains accurate regardless of empty rows.

Why is SUMPRODUCT preferred for simplicity?

SUMPRODUCT combined with COUNTIF function doesn’t require array formula entry (no Ctrl + Shift + Enter), making it more accessible for many users. It calculates results quickly and works reliably even in older Excel versions without advanced functions.

Can I count duplicates only once across columns?

Yes, you can count unique values spanning multiple columns by wrapping the range in the UNIQUE function, like UNIQUE(A1:C10), and then using the ROWS or COUNTA function to count distinct values across that entire multi-column range.

Wrapping Up

In this tutorial, we learned how to count duplicate values only once in Excel whether your data includes case-sensitive text, numbers, or mixed entries. Depending on your version of Excel and dataset type, you can pick from formulas like SUMPRODUCT, FREQUENCY, or even use Excel’s built-in Advanced Filter or the modern UNIQUE function. Feel free to download the practice file and share your feedback.