Excel SUMPRODUCT Function to Count Unique Values with Criteria

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel SUMPRODUCT function to count unique values based on criteria

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.

Dataset to Count Unique Values with Single criteria Using Excel SUMPRODUCT, IFERROR and COUNTIF Functions

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.

Select cell G8, expand the drop-down and choose Desktop

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

=SUMPRODUCT(($C$6:$C$14=$G$6)*(IFERROR(1/COUNTIFS($B$6:$B$14,$B$6:$B$14,$C$6:$C$14,$G$6),0)))

Select cell G10, insert the given formula

This formula is not case-sensitive. So if you type “desktop,” not “Desktop,” the formula will still work perfectly.

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

Press the Enter key to see 2 in cell G10, as two unique brands 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.

Using Excel SUMPRODUCT, IFERROR and COUNTIF Functions to Count Unique Values with Single Text Criteria

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

Easier Alternative Formula for Excel 2021 Users:

If you use Excel 2021 or Excel for 365, you can use the following formula.

=IF(G6<>"",COUNTA(UNIQUE(FILTER($B$6:$B$14,$C$6:$C$14=$G$6))),0)

Press the Enter key to see 2 in cell G10, as two unique brands sell desktops


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.

Dataset to Count Unique Values with Single number criteria Using Excel SUMPRODUCT, IFERROR and COUNTIF Functions

Follow these steps:

Step 1: Go to cell G6 ⇒ Type 120.

Select cell G7 and type 120 as Units Sold for comparison

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

=SUMPRODUCT(($E$6:$E$14>=$G$7)*(IFERROR(1/COUNTIFS($B$6:$B$14,$B$6:$B$14,$E$6:$E$14,">="&$G$7),0)))

Select cell G11 and insert the given 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.

Press Enter to see 4 in cell G11 as there are four brands that sell more than 120 units

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

Using Excel SUMPRODUCT, IFERROR and COUNTIF Functions to Count Unique Values with Single Number Criteria

Note:

  • If you want to apply other criteria operators to count unique values, then just change the operator inside the formula. For example, replace >= with < to apply “less than” criteria.
  • If you use Microsoft 365 or Excel 2021 version, you can use the following formula to count unique values based on the number criterion.

=IF($G$7<>"",COUNTA(UNIQUE(FILTER($B$6:$B$14,$E$6:$E$14>=$G$7))),0)

Press Enter to see 4 in cell G11 as there are four brands that sell more than 120 units


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).

Dataset to Count Unique Values with Multiple Criteria Using CEILING, SUMPRODUCT, IFERROR and COUNTIF Functions

Follow these steps:

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

Select cell G6, expand the drop-down and choose Notebook

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

Select cell G9 and type 120 as Units Sold for comparison

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

=CEILING(SUMPRODUCT((IFERROR(1/IF($E$6:$E$14>=$G$9,1,0),0))*(IFERROR(1/COUNTIFS($B$6:$B$14,$B$6:$B$14,$C$6:$C$14,$G$6),0))*(IFERROR(1/IF($C$6:$C$14=$G$6,1,0),0))),1)

Select cell G13 and insert the given 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.

Press Enter to see 2 as two unique brands that sell notebook and sales quantity greater or equal to 120

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

Output of Using CEILING, SUMPRODUCT, IFERROR and COUNTIF Functions to Count Unique Values with Multiple Criteria

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

=CEILING(SUMPRODUCT((IFERROR(1/IF($E$6:$E$14<$G$9,1,0),0))*(IFERROR(1/COUNTIFS($B$6:$B$14,$B$6:$B$14,$C$6:$C$14,$G$6),0))*(IFERROR(1/IF($C$6:$C$14=$G$6,1,0),0))),1)

Count unique brands that sell notebook and sales quantity less than 120

Alternative Formula for Excel 2021 Users:

  • For greater than or equal:

=UNIQUE(IF(ISERROR(FILTER($B$6:$B$14,($C$6:$C$14=$G$6)*($E$6:$E$14>=$G$9))),0,COUNTA(UNIQUE(FILTER($B$6:$B$14,($C$6:$C$14=$G$6)*($E$6:$E$14>=$G$9))))))

Press Enter to see 2 as two unique brands that sell notebook and sales quantity greater or equal to 120

  • For less than:

=UNIQUE(IF(ISERROR(FILTER($B$6:$B$14,($C$6:$C$14=$G$6)*($E$6:$E$14<$G$9))),0,COUNTA(UNIQUE(FILTER($B$6:$B$14,($C$6:$C$14=$G$6)*($E$6:$E$14<$G$9))))))

Count unique brands that sell notebook and sales quantity less than 120


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.

Dataset to Count Unique Text Values Using ISTEXT and COUNTIF with SUMPRODUCT

Follow these steps:

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

=SUMPRODUCT(ISTEXT(B6:B17)/COUNTIF(B6:B17,B6:B17&""))

Select cell D6 and insert the given 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.

Press Enter to see 4 in cell D4 thus, we count unique text values using ISTEXT and COUNTIF with SUMPRODUCT

Alternative Formula for Excel 2021 Users:

=COUNTA(UNIQUE(FILTER($B$6:$B$17,ISTEXT($B$6:$B$17))))

Press Enter to see 4 in cell D4 thus, we count unique text values Using COUNTA, UNIQUE, FILTER and ISTEXT functions


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.

Dataset to Count Unique Numbers Using ISNUMBER and COUNTIF with SUMPRODUCT

Follow these steps:

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

=SUMPRODUCT(ISNUMBER(B6:B17)/COUNTIF(B6:B17,B6:B17&""))

Select cell D6 and insert the given 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.

Alternative Formula for Excel 2021 Users:

=COUNTA(UNIQUE(FILTER($B$6:$B$17,ISNUMBER($B$6:$B$17))))

Press Enter to see 4 in cell D4 thus, we count unique number values using COUNTA, UNIQUE, FILTER and ISNUMBER


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo