Solved: Using unique with sumproduct

📌 The Problem Explained

In many data analysis scenarios, you have a list containing duplicate entries—such as a sales log where the same customer appears multiple times. You need to calculate the total count of unique items without manually filtering the list or using a Pivot Table.

While the modern UNIQUE function is powerful, combining it with SUMPRODUCT allows you to handle array calculations efficiently, even when you need to add conditional logic or ensure compatibility across different data processing environments. The challenge is that SUMPRODUCT expects numbers, while UNIQUE returns an array of values, requiring a “bridge” to count them correctly.

💡 The Quick Solution

To count the number of unique values in a range (e.g., A2:A10), use this formula:

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

Note: If you are using Microsoft 365 and want to use the specific UNIQUE function inside SUMPRODUCT to count unique items while ignoring blanks, use:

=SUMPRODUCT(--(UNIQUE(A2:A10)<>""))

🛠️ Step-by-Step Breakdown

The following table breaks down the logic of the SUMPRODUCT + COUNTIF approach, which is the industry standard for “Unique” calculations.

Step	Component	Logic	Purpose
1	COUNTIF(A2:A10, A2:A10)	Creates an array of how many times each item appears.	To identify the frequency of every cell in the range.
2	1 / [Array]	Divides 1 by the frequency (e.g., if “Apple” appears 3 times, it becomes 1/3).	Ensures that when the fragments are summed, they equal exactly 1.
3	SUMPRODUCT(...)	Sums all the fractions (e.g., 1/3 + 1/3 + 1/3 = 1).	Converts the array of fractions into a single total count of unique units.
4	-- (Double Unary)	Used in the UNIQUE version to convert TRUE/FALSE to 1 and 0.	Allows SUMPRODUCT to mathematically process logical comparisons.

To implement this in your spreadsheet, navigate to the Home tab, select your target cell, and enter the formula in the Formula Bar.

⚠️ Common Mistakes to Avoid

• Empty Cells in Range: If your range (e.g., A2:A100) contains blank cells, the 1/COUNTIF formula will return a #DIV/0! error. To fix this, use: =SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10, A2:A10&"")).
• Large Ranges: SUMPRODUCT with COUNTIF can be slow on very large datasets (10,000+ rows). If performance lags, consider using Data > Remove Duplicates on a copy of the data or switching to a Pivot Table.
• Data Types: Ensure your data is consistent. Excel treats “123” (text) and 123 (number) as two unique values. Use Data > Text to Columns to standardize your formatting if the count seems off.