Excel SUMPRODUCT Function to Count Unique Values with Criteria

We have a dataset with Brand names, Device Categories, Model No, and Units Sold. We want to know how many brands (unique values) are selling notebooks (criteria) only.

Excel SUMPRODUCT function to count unique values based on 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. SUMPRODUCT can return values based on criteria, but you have to combine COUNTIF or COUNITFS with it to count unique values.

Method 1 – Count Unique Values with a Single Criterion Using Excel SUMPRODUCT, IFERROR, and COUNTIFS Functions

Case 1.1 – Counting Unique Values with a Single Text Criterion

In cell G6, we specified a Device Category, for example, desktop. 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:

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 G10 and 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.

Press the Enter key for the result.

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

Case 1.2 – Counting Unique Values with a Single Number Criterion

We put a number in cell G7, for example, 120. In cell G10, we are going to count the unique brand names that sold at least 120 units of product.

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

Follow these steps:

Go to cell G6.
Type 120.

Select cell G7 and type 120 as Units Sold for comparison

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

Press the Enter key for the result.

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

You can 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, change the operator inside the formula. For example, replace >= with < to apply “less than” criteria.
If you use the 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

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

We’ll count how many brands have sold notebooks (criterion 1) and have sold at least 120 units (criterion 2).

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

Follow these steps:

Select cell G6.
Choose Notebook as the category.

Select cell G9.
Type 120 as Units Sold for Comparison.

Select cell G9 and type 120 as Units Sold for comparison

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

Press Enter.

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

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, use 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 Formulas 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

Method 3 – Count Unique Text Values Using ISTEXT and COUNTIF with SUMPRODUCT

We have a dataset with a column titled Names / 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