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:
array1– Range of cells along with columns, rows, or both.
[array2[,[array3],…– More arrays to input if needed.
Returns the SUM of the products from the corresponding ranges of arrays.
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-
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.
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.
➤ Select Cell G15 & type-
➤ Press Enter & you’ll see a total of 5 brands are enlisted here.
🔎 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:
➤ 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–
After you press Enter, now you’ll see no #DIV/0 errors as this formula will exclude the blank cells & won’t count them.
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.
➤ In Cell G17, we have to type based on our dataset-
➤ Then press Enter & you’ll notice 2 counts have been found under our 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.
➤ In Cell G15, our formula for this criteria will be-
➤ After pressing Enter, you’ll see the resultant value as 9 counts in total.
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.
➤ The required formula in Cell G17 will be-
➤ Press Enter & you’ll find the total count as 2.
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.
➤ For our given dataset, type in Cell D12–
➤ 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.
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.
➤ In Cell D12, the formula will be-
➤ 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.
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-
Here, the syntax of this COUNTIF function is-
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-
And last of all, here, in another important case which incorporates AND together with OR criteria, our formula will be-
It’ll count the sales of Lenovo & Asus notebooks with more than $100 profits from the table.
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.
📌 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.
📌 Step 3:
➤ From the Value Field Settings dialogue box, choose Max from the scrollings under the ‘Summarize Value By’ bar.
➤ Press OK.
📌 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.
📌 Step 5:
➤ Similarly, select Notebook from Device Category options.
➤ Press OK & you’re done.
Here, you’re seeing now the maximum profit from all sales of Lenovo & Asus notebooks.
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.