Excel SUMPRODUCT Function to Count Unique Values with Criteria

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)

SUMPRODUCT Function to Count Unique Values in Excel

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

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.

Easy Ways to Use SUMPRODUCT Function to Count Unique Values with Criteria in Excel


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.

Count Unique Values from a Range Without Blank Cells as Criteria Using SUMPRODUCT Function in Excel

🔎 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.

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.

Use SUMPRODUCT Function to Count Unique Values with Blank Cells as Criteria in Excel

🔎 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}

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.

SUMPRODUCT Function to Count Unique Values under Multiple AND Criteria in Excel

Here, in the SUMPRODUCT function, we inserted 3 criteria. Firstly, we checked if “HP” is in cell range B5:B13, then checked if “Desktop” is in cell range C5:C13 and finally, if the values in cell range D5:D13 are greater than 100.

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.

Count Unique Values Using SUMPRODUCT Function under Multiple OR Criteria in Excel

In the SUMPRODUCT function, we inserted 2 criteria by using the Plus sign (+) between them. We checked if “Lenovo” and “Asus” are in cell range B5:B13.

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.

Use SUMPRODUCT Function to Count Unique Values with Both AND & OR Criteria in Excel

🔎 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.

Count Unique Cells That Contain Only Texts as Criteria with SUMPRODUCT 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.

Excel Count Unique Values with Criteria Sumproduct

🔎 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}

Read more: How to Use COUNTIF for Unique Text


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.

SUMPRODUCT Function to Count Unique Cells That Contain Only Numbers as Criteria in Excel

🔎 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}

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)

Use COUNTIF & COUNTIFS Functions as Alternatives to Use SUMPRODUCT to Count Unique Values with Criteria in Excel

Here, we inserted cell range B5:B13 as the range and Cell B5 as the criteria in the COUNTIF function.
  • 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")

In the above COUNTIFS function, we inserted 3 ranges followed by 3 cell ranges to count  unique values with multiple criteria.
  • 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.

Here, we used the COUNTIF function to count values for multiple criteria in multiple cell ranges. Then, add those values using the SUM function.

Read more: COUNTIFS Unique Values in Excel


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.

Apply Excel Pivot Table as Alternative to Use SUMPRODUCT to Count Unique Values with Criteria

  • 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.


Practice Section

In the article, you will find an Excel workbook like the image given below to practice on your own.

Practice Section

➥ Related: How to Count Unique Values in Excel Using Pivot Table


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.


Related Articles

Nehad Ulfat

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