Nehad Ulfat
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 Excel 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.
- 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(B5:B8*C5:C8)
7 Easy Ways to Use SUMPRODUCT Function to Count Unique Values with Criteria
Now let’s start with our prime dataset. A computer shop has made a list of 9 sales along with computer brands, device types & profits. Now, we will show you how you can use the SUMPRODUCT Function to count unique values with different criteria.
1. Count Unique Values from a Range Without Blank Cells
Firstly, we want to know how many brands are there. We’ll incorporate SUMPRODUCT & COUNTIF functions to do that.
Steps:
- In the beginning, Select Cell F5 and insert the following formula.
=SUMPRODUCT(1/COUNTIF(B5:B13,B5:B13))- Then, press Enter & you’ll see a total of 5 brands are enlisted here.
🔎 How Does the 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:
{2;2;2;2;1;2;2;2;2}
- Now, think of one brand, Lenovo. There are 2 counts here of this brand. Next, 1/50= 0.50, as we’re getting this value 2 times, so all these 2 values will be added up under the SUMPRODUCT function & then the resultant count will be: 1(0.50+0.50).
- Thus, the brand count of Lenovo will be shown as 1. Similarly, all other brand counts will follow the procedures accordingly.
Read More: How to Count Frequency of Unique Values in a Column in Excel
2. Use SUMPRODUCT Function to Count Unique Values 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.
Steps:
- Firstly, select Cell F5 and insert the following formula.
=SUMPRODUCT((B5:B15<>"")/COUNTIF(B5:B15,B5:B15&""))- After that, press Enter.
- Now, you’ll see no #DIV/0 errors as this formula will exclude the blank cells & won’t count them.
🔎 How Does the Formula Work?
- COUNTIF(B5:B15,B5:B15&””)—–> The COUNTIF function returns the count value of a given condition.
- Output: {{2;1;2;2;3;2;2;1;2;3;3}}
- SUMPRODUCT((B5:B15<>””)/COUNTIF(B5:B15,B5:B15&””)))—–> The SUMPRODUCT function is used to firstly multiply values of arrays and then add up all those values.
- SUMPRODUCT(({TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE})/{2;1;2;2;3;2;2;1;2;3;3})—–> turns into
- Output: {5}
- SUMPRODUCT(({TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE})/{2;1;2;2;3;2;2;1;2;3;3})—–> turns into
3. 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. Follow the steps given below to do it on your own dataset.
Steps:
- In Cell F9, we have to type based on our dataset-
=SUMPRODUCT((B5:B13="HP")*(C5:C13="Desktop")*(D5:D13>100))- Then, press Enter & you’ll notice 2 counts have been found under our criteria.
Read More: Excel VBA: Count Unique Values in a Column (3 Methods)
4. Count Unique Values Using SUMPRODUCT Function 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:
- Firstly, in Cell F7, our formula for this criterion will be-
=SUMPRODUCT((B5:B13="Lenovo")+(B5:B13="Asus"))- Then, press Enter, you’ll see the resultant value as 4 counts in total.
Read More: How to Count Unique Values in Multiple Columns in Excel (5 Ways)
5. Use SUMPRODUCT Function to Count Unique Values with Both AND & 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. Follow the steps given below to count unique values with different criteria using the SUMPRODUCT functions in Excel.
Steps:
- To start with, select Cell F9 and insert the following formula.
=SUMPRODUCT(((B5:B13="Lenovo")+(B5:B13="Asus")) *(C5:C13="Notebook")*(D5:D13>100))- After that, press Enter & you’ll find the total count as 2.
🔎 How Does the Formula Work?
In the SUMPRODUCT function, we inserted both AND & OR criteria.
- Firstly, we checked if “Lenovo” and “Asus” are in cell range B5:B13.
- Then, we checked if “Notebook” is in cell range C5:C13.
- Finally, if the values in cell range D5:D13 are greater than 100.
- 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.
6. Count 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 count of names only & if a name appears multiple times, it’ll be counted only once. Our formula will exclude the blank cells too. To do that we will use the ISTEXT, COUNTIF and SUMPRODUCT functions in Excel.
Steps:
- Firstly, or our given dataset, type in Cell D5–
=SUMPRODUCT(ISTEXT(B5:B16)/COUNTIF(B5:B16,B5:B16&""))- Secondly, press Enter & you’ll see a total of 6 unique names have been found through our formula.
- However, if you notice the name Alex has 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.
🔎 How Does the Formula Work?
- ISTEXT(B5:B16))—–> The ISTEXT function can check if the given data is a text or not.
- Output:{TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE}
- COUNTIF(B5:B16,B5:B16&””)—–> The COUNTIF function returns the count value of a given condition.
- Output: {2;1;1;1;1;1;1;1;2;1;1;1}
- SUMPRODUCT(ISTEXT(B5:B16)/COUNTIF(B5:B16,B5:B16&””))—–> The SUMPRODUCT function is used to firstly multiply values of arrays and then add up all those values.
- SUMPRODUCT({TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE}/{2;1;1;1;1;1;1;1;2;1;1;1})—–> turns into
- Output: {6}
- SUMPRODUCT({TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE}/{2;1;1;1;1;1;1;1;2;1;1;1})—–> turns into
Read More: How to Use COUNTIF for Unique Text (8 Easiest Ways)
7. SUMPRODUCT Function to Count Cells That Contain Only Numbers
In this section, we’ll count numbers only excluding all names or texts along with blank cells. We will use the ISNUMBER, COUNTIF and SUMPRODUCT functions to count unique values with criteria in Excel.
Steps:
- In the beginning, select Cell D5 and insert the following formula.
=SUMPRODUCT(ISNUMBER(B5:B16)/COUNTIF(B5:B16,B5:B16&""))- Finally, press Enter & you’re done.
- You’ll find a total of 4 instances as the resultant value.
- Like the previous one, this formula also filters duplicate values.
🔎 How Does the Formula Work?
- ISNUMBER(B5:B16)—–> The ISNUMBER function can check if the given data is a number or not.
- Output:{FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE}
- COUNTIF(B5:B16,B5:B16&””)—–> The COUNTIF function returns the count value of a given condition.
- Output: {2;1;1;1;1;1;1;1;2;1;1;1}
- SUMPRODUCT(ISNUMBER(B5:B16)/COUNTIF(B5:B16,B5:B16&””))—–> The SUMPRODUCT function is used to firstly multiply values of arrays and then add up all those values.
- SUMPRODUCT({FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE}/{2;1;1;1;1;1;1;1;2;1;1;1})—–> turns into
- Output: {4}
- SUMPRODUCT({FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE}/{2;1;1;1;1;1;1;1;2;1;1;1})—–> turns into
Read More: How to Count Unique Values in Filtered Column in Excel (5 Methods)
Alternatives of Using SUMPRODUCT to Count Unique Values with Criteria in Excel
Additionally, if you think you want to apply some other formulas or functions then we have two simple & handy alternatives for you.
1. Use COUNTIF & COUNTIFS Functions
In our first case, we will use the COUNTIF and COUNTIFS functions to count unique values with criteria.
Steps:
- Firstly, if we want to know the total sales counts of Lenovo devices only then Enter the formula in Cell F5.
=COUNTIF(B5:B13,B5)
- Then, press Enter.
- 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(B5:B13,B5,C5:C13,C5,D5:D13,">100")
- Lastly, here, in another important case which incorporates AND together with OR criteria, our formula will be-
=SUM(COUNTIFS(B5:B13,{"Lenovo","Asus"}, C5:C13,C5,D5:D13,">100"))- Thus, you will get the count value of the sales of Lenovo & Asus notebooks with more than $100 profits from the table.
Read More: COUNTIFS Unique Values in Excel (4 Easy Ways)
2. Apply Excel Pivot Tables
With Pivot Table filtering, we can find out a wide range of output. Here we’ll go for only one criterion & which is finding the maximum profit from Lenovo & Asus notebooks.
Steps:
- To start with, select the whole array of data(B4:D13).
- Then, go to the Insert tab >> select Pivot Table >> click on From Table/Range.
- Now, the PivotTable from table or range box will appear with the selected range.
- Then, select Existing Worksheet & a specific cell for output data.
- Next, press OK & a new sidebar window named Pivot Table Fields will appear.
- After that, drag the Brand & Device Category headers to the Filters area with your mouse.
- Similarly, tug the Profit header to the Values field.
- Further, click on the drop-down from the Profit field & select Value Field Settings.
- Now, from the Value Field Settings dialogue box, choose Max from the scrolling under the ‘Summarize value field by’ bar.
- Finally, press OK.
- Now, go to your worksheet where you’ll find a filtered table.
- Then, from Filter options of Brand, firstly turn on the Select Multiple Items option.
- Next, select HP & Lenovo and then press OK.
- Similarly, select Notebook from the Device Category options.
- Finally, press OK & you’re done.
- Here, you’re now seeing the maximum profit from all sales of Lenovo & Asus notebooks.
Read More: How to Count Unique Values Using Excel Pivot Table (3 Ways)
Practice Section
In the article, you will find an Excel workbook like the image given below to practice on your own.
Conclusion
These are some methods you can use to count unique values with different criteria using the SUMPRODUCT functions in Excel. 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, 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.
