Excel SUMPRODUCT Function to Count Unique Values with Criteria

Using the Excel SUMPRODUCT function to count unique values with criteria means counting unique numbers or texts based on single or multiple conditions from a dataset.

Say, we have a dataset with Brand names, Device Categories, Model No, and Units Sold, like the following image. You see, there are Lenovo, HP, Asus, Acer, and Dell- a total of 5 unique brand names. They sell 3 types of devices: desktop, phone, and notebook.

Suppose you want to know how many brands (unique values) are selling notebooks (criteria) only. There are 5 unique brand names, but only 3 brands (Lenovo, Asus, and Acer) are selling notebooks (HP or Dell is not selling notebooks). Similarly, only 2 brands (Asus and Acer) are selling phones.

In this Excel tutorial, I will provide some Excel SUMPRODUCT formulas to easily get this count (how many unique values match the criteria).

In summary, we shall use the Excel SUMPRODUCT function to:

– Count unique values based on single criteria
– Count unique values based on multiple criteria
– Count unique text values from a mixed dataset
– Count unique numbers from a mixed dataset

We shall also show an alternative formula instead of the SUMPRODUCT function to count unique values with criteria.

Why Use SUMPRODUCT to Count Unique Values with Criteria?

You could use UNIQUE and FILTER functions instead of SUMPRODUCT. However, the UNIQUE function is not available in Excel before 2021. The FILTER function is available from Excel 2019.

So, if your Excel version is older than 2019, SUMPRODUCT can be a suitable option for you to count unique values based on conditions. But note that, SUMPRODUCT can return values based on criteria, but you have to combine COUNTIF or COUNITFS with it to count unique values.

Note: We have used Excel for Microsoft 365 to prepare this article.

1. Count Unique Values with Single Criteria Using Excel SUMPRODUCT, IFERROR, and COUNTIFS Functions

Here, we will count unique values with one criterion with the SUMPRODUCT, IFERROR, and COUNTIFS functions.

1.1 Counting Unique Values with Single Text Criteria

In the following image, I have a dataset with 4 columns: Brand, Device Category, Model No, and Units Sold. In cell G6, we specify a Device Category, for example, desktop. Later, we are going to count the unique brand names that sell desktops in cell G10.

Follow these steps:

Step 1: Go to cell G6 ⇒ Type desktop.

We have created a drop-down list so that you can easily choose a category name from the list. Use that, or type manually.

Step 2: Select cell G10 ⇒ Insert the following formula.

Step 3: Press the Enter key ⇒ You get 2, i.e., 2 brands (HP and Dell) sell desktops.

To know how many brands sell phones or notebooks, change the device category in cell G6 to phone or notebook, like the following GIF.

We have applied the Conditional Formatting feature within the dataset so that the records can easily be noticed.

1.2 Counting Unique Values with Single Number Criteria

Counting unique values for number criteria demands a small change in the formula of Case 1.1. So, we have shown that separately.

This time, we input a number in cell G7, for example, 120. Later, in cell G10, we are going to count the unique brand names that sell more than or equal to 120 units of product.

Follow these steps:

Step 1: Go to cell G6 ⇒ Type 120.

Step 2: Select cell G10 ⇒ Insert the following formula.

Step 3: Press the Enter key ⇒ You get 4, i.e., 4 brands (Lenovo, HP, Dell, and Asus) sell more than or equal to 120 units of product.

You can now change the number in cell G6 for comparison, like the following GIF.

2. Count Unique Values with Multiple Criteria Using CEILING, SUMPRODUCT, IFERROR, and COUNTIF Functions

In this example, we will see how to count unique values based on multiple conditions using the CEILING, SUMPRODUCT, IFERROR, and COUNTIFS functions.

Now, I want to count how many brands have sold notebooks (criteria-1) greater than or equal to 120 units (criteria-2).

Follow these steps:

Step 1: Select cell G6 ⇒ Choose Notebook as the category.

Step 2: Select cell G9 ⇒ Type 120 as Units Sold for Comparison.

Step 3: Select cell G13 ⇒ Insert the following formula.

Step 4: Press Enter ⇒ You will see 2 in cell G13 as two unique brands that sell notebooks and have a sales quantity greater than or equal to 120.

Like the following GIF, you can also count unique brands by changing the device category and number of units sold.

To count the number of brands that sell notebooks and sales quantity less than 120 using the SUMPRODUCT formula:

3. Count Unique Text Values Using ISTEXT and COUNTIF with SUMPRODUCT

In this example, you will learn how to count unique text values from a range of cells using SUMPRODUCT, ISTEXT, and COUNTIF. Here, the criterion is filtering the “text values” from a mixed dataset.

Assume I have a dataset with a column titled Name / IDs like the following image. Under this column, there are some names and IDs in the range B6:B17. In cell D6, we will count the unique names, ignoring empty cells.

Follow these steps:

Step 1: Select cell D6 ⇒ Insert the following formula.

Step 2: Press Enter ⇒ You will see 4 as output as there are four unique names in the Names / IDs column: Alen, Lee, Max, and Alisa.

4. Count Unique Numbers Using ISNUMBER and COUNTIF with SUMPRODUCT

Here, I will show how to count unique numbers using the SUMPRODUCT, ISNUMBER, and COUNTIF functions. Here, the criterion is used for counting the numbers from a mixed dataset.

I am using the same dataset. But this time, I will count the unique IDs that are in number format, ignoring the empty cells within the range B6:B17.

Follow these steps:

Step 1: Select the cell D6 ⇒ Insert the following formula.

Step 2: Press Enter ⇒ You will see 4 as output as there are four unique IDs in the Names / IDs column: 160, 129, 168, and 195.

Download Practice Workbook

This is how we can count unique values with criteria using SUMPRODUCT in Excel. If you are working with a single criterion, combine the SUMPRODUCT, IFERROR, and COUNTIF functions to count unique values. For multiple criteria, join the CEILING, SUMPRODUCT, IFERROR, and COUNTIF functions to count unique values. For counting only text values, use ISTEXT and COUNTIF with SUMPRODUCT. For counting only numbers, use ISNUMBER and COUNTIF with SUMPRODUCT. If you are a Microsoft Excel 365, Excel 2021, or Excel 2019, then you can also go through the alternative ideas. If you have any questions or suggestions, just leave us a comment.

<< Go Back to Count | Unique Values | Learn Excel