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 G8, expand the drop-down and choose Desktop

  • 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 G6, expand the drop-down and choose Notebook

  • 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

Follow these steps:

  • Select cell D6 andinsert the following formula.

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

Select cell D6 and insert the given formula

  • Press Enter.

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


Method 4 – Count Unique Numbers Using ISNUMBER and COUNTIF with SUMPRODUCT

We will count the unique IDs that are in the 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:

  • 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

  • Press Enter.

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 the Practice Workbook


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

Get FREE Advanced Excel Exercises with Solutions!
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