Count Unique Values with Criteria by SUMPRODUCT in Excel

In MS Excel, the SUMPRODUCT function provides plenty of complex calculations from the arrays which can be used to count unique values under single or multiple criteria. In this article, I’ll show how you can utilize this SUMPRODUCT function to count unique data from a large range of cells with different criteria.

Download Practice Workbook

You can download the practice workbook that we’ve used to prepare this article.


Introduction to SUMPRODUCT Function

Before starting on the procedure of counting unique values with the SUMPRODUCT function, let us have a look at how this function works.

  •  Formula Syntax:
SUMPRODUCT(array1,[array 2],[array3],…..)
  • Argument:

array1– Range of cells along with columns, rows, or both.

[array2[,[array3],…– More arrays to input if needed.

  • Function:

Returns the SUM of the products from the corresponding ranges of arrays.

  • Example:

Here’s a table below where we want to multiply values in Column B with values alongside in Column C and then we’ll get a total sum of all the products.

So, the formula will be for this function-

=SUMPRODUCT(B2:B5*C2:C5)

It means, values in Cells(B2 to B5) are being multiplied with the corresponding values from the Cells(C2 to C5) in Column C. As we’re using SUMPRODUCT function, so automatically the products will add up to 220 in row 6 under the table.

sumproduct introduction


Counting Unique Values Only from a Column or a Range with SUMPRODUCT without Blank Cells

Now let’s start with our prime dataset. A computer shop has made a list of 23 sales of 10 days along with computer brands, device types & profits. At first, we want to know how many brands are there. We’ll incorporate SUMPRODUCT & COUNTIF functions.

📌 Steps:

➤ Select Cell G15 & type-

=SUMPRODUCT(1/COUNTIF(C5:C27,C5:C27))

➤ Press Enter & you’ll see a total of 5 brands are enlisted here.

count unique values with sumproduct single crriteria

🔎 How Does This Formula Work?

➤ Technically COUNTIF function here counts all the brand names every time it goes through each cell in Column C & the resultant array appears as:

{4;5;5;3;6;4;5;3;5;6;6;4;5;6;5;4;6;5;5;6;3;5;5}

➤ Now, think of one brand Lenovo. There are 4 counts here of this brand. Now, 1/25= 0.25, as we’re getting this value 4 times, so all these 4 values will be added up under SUMPRODUCT function & then the resultant count will be: 1(0.25+0.25+0.25+0.25). Thus the brand count of Lenovo will be shown as 1. Similarly, all other brand counts will follow the procedures accordingly.


Counting Unique Values Only from a Column or a Range with SUMPRODUCT with Blank Cells

Let’s think of a case if we find blank cells in the chart. SUMPRODUCT function won’t work here with the previous formula & will show #DIV/0 Error. So, we have to modify the formula a bit & type in Cell G15

=SUMPRODUCT((C5:C27<>"")/COUNTIF(C5:C27,C5:C27&""))

After you press Enter, now you’ll see no #DIV/0 errors as this formula will exclude the blank cells & won’t count them.

count unique values with sumproduct blank cell criteria


SUMPRODUCT Function to Count Unique Values under Multiple ‘AND’ Criteria

Now we want to add multiple criteria to our findings. We want to know the number of HP desktop sales that had profits of more than $100.

📌 Steps:

➤ In Cell G17, we have to type based on our dataset-

=SUMPRODUCT((C5:C27="HP")*(D5:D27="Desktop")*(E5:E27>100))

➤ Then press Enter & you’ll notice 2 counts have been found under our criteria.

count unique values with sumproduct multiple AND criteria


SUMPRODUCT Function to Count Unique Values under Multiple ‘OR’ Criteria

Here’s another case where we want to find out how many sales of Lenovo or Asus brands have been listed in the table or chart.

📌 Steps:

➤ In Cell G15, our formula for this criteria will be-

=SUMPRODUCT((C5:C27="Lenovo")+(C5:C27="Asus"))

➤ After pressing Enter, you’ll see the resultant value as 9 counts in total.

count unique values with sumproduct multiple or criteria


SUMPRODUCT Function to Count Unique Values under Multiple ‘AND’ along with ‘OR’ Criteria

This time we’ll incorporate both AND along with OR criteria. We want to find the total sales count of Lenovo & Asus notebooks that had profits of more than $100.

📌 Steps:

➤ The required formula in Cell G17 will be-

=SUMPRODUCT(((C5:C27="Lenovo")+(C5:C27="Asus")) *(D5:D27="Notebook")*(E5:E27>100))

➤ Press Enter & you’ll find the total count as 2.

count unique values with sumproduct AND OR criteria

If you’ve noticed carefully, you’ll find that while constructing the formulas, the difference between the two criteria- AND or OR is we have to input Commas (,) in AND criteria & for OR criteria, we need to put a Plus(+) in the function bar to add two criteria.


Counting Unique Cells That Contain Only Texts with SUMPRODUCT

Now, we’ll deal with another dataset where a list of names (one with duplicates), some ID numbers & blank cells are present. We want to find the total counts of names only & if a name appears multiple times it’ll be counted only once. Our formula will exclude the blank cells too.

📌 Steps:

➤ For our given dataset, type in Cell D12

=SUMPRODUCT(ISTEXT(B5:B20)/COUNTIF(B5:B20,B5:B20&""))

➤ Now press Enter & you’ll see a total of 7 unique names have been found through our formula. If you notice the names Alex & Marcus have appeared multiple times but each of them has been counted only once. We had blank cells too in the column which have not been counted.

count unique texts with sumproduct


Counting Unique Cells That Contain Only Numbers with SUMPRODUCT

In this section, we’ll count numbers only excluding all names or texts along with blank cells.

📌 Steps:

➤ In Cell D12, the formula will be-

=SUMPRODUCT(ISNUMBER(B5:B20)/COUNTIF(B5:B20,B5:B20&""))

➤ Press Enter & you’re done. You’ll find a total of 4 instances as resultant value. Like the previous one, this formula also filters duplicate values & that’s why the number 168 has been counted only once though it has appeared twice in the list.

count unique numbers only criteria with sumproduct


Alternatives to SUMPRODUCT to Count Unique Values with Criteria

If you think you want to apply some other formulas or functions then we have two simple & handy alternatives for you.

1. With COUNTIF & COUNTIFS Functions

In our first case, if we want to know the total sales counts of Lenovo devices only then the formula will be-

=COUNTIF(C5:C27, C5)

Here, the syntax of this COUNTIF function is-

COUNTIF(range,criteria) 

count unique values with sumproduct alternative countif single criteria

Now, if we want to add more criteria like the sales counts of HP desktop with more than $100 profits for each, then our formula will be-

=COUNTIFS(C5:C27,C5,D5:D27,D5,E5:E27,">100")

count unique values with sumproduct alternative countifs multiple criteria

And last of all, here, in another important case which incorporates AND together with OR criteria, our formula will be-

=SUM(COUNTIFS(C5:C27,{"Lenovo","Asus"}, D5:D27,D5,E5:E27,">100"))

It’ll count the sales of Lenovo & Asus notebooks with more than $100 profits from the table.

count unique values with sumproduct alternative multiple AND OR criteria


2. With Pivot Table

With Pivot Table filtering, we can find out a wide range of output. Here we’ll go for only one criterion & that is finding the maximum profit from Lenovo & Asus notebooks.

📌 Step 1:

➤ Select the whole array of data(C5:E27).

➤ From the Insert tab, select Pivot Table, a dialogue box like in the picture below will appear.

➤ Select Existing Worksheet & a specific cell for output data.

➤ Press OK & a new sidebar window named Pivot Table Fields will appear.

count unique values with sumproduct pivot table multiple criteria

📌 Step 2:

➤ Drag Brand & Device Category headers to the Filters area with your mouse.

➤ Similarly, tug the Profit header to the Values field.

➤ Click on the drop-down from the Profit field & select Value Field Settings.

count unique values with sumproduct pivot table multiple criteria

📌 Step 3:

➤ From the Value Field Settings dialogue box, choose Max from the scrollings under the ‘Summarize Value By’ bar.

➤ Press OK.

count unique values with sumproduct pivot table multiple criteria

📌 Step 4:

➤ Now go to your worksheet where you’ll find a filtered table.

➤ From Filter options of Brand, select HP & Lenovo and then Press OK.

count unique values with sumproduct pivot table multiple criteria

📌 Step 5:

➤ Similarly, select Notebook from Device Category options.

➤ Press OK & you’re done.

count unique values with sumproduct pivot table multiple criteria

Here, you’re seeing now the maximum profit from all sales of Lenovo & Asus notebooks.

count unique values with sumproduct pivot table multiple criteria


Concluding Words

I hope that all the major methods along with two alternatives should cover and function perfectly with your regular Excel works. If you think I’ve missed a method that should have been added to then please let me know in the comment section. You can also check out our other informative & useful articles related to Excel functions on this website.


Related Articles You May Find Interesting

COUNTIF with Multiple Criteria in Different Columns

How to Extract Unique Values Based on Criteria in Excel

Excel Formula Count Unique Values

Excel Count Number of Occurrences of Each Value in a Column

How to Find Unique Values from Multiple Columns in Excel

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo