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)`

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

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

### 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}**

### 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, 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.

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

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

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

🔎** 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)`

**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")`

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

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

- 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 profi**t 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.

**➥ 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.