Once you are familiar with Excel, one thing may come to your mind often, how you can use the **SUMPRODUCT** function with multiple criteria. In this article, we are going to show you the way of using **SUMPRODUCT** for multiple criteria lookup. Let’s begin.

Today we have chosen a basic table of an imaginary company that sells bags in different cities. The table has four columns; *City, Seller Name, Product, Present Quantity. *

Using this table we will show how we can use **SUMPRODUCT** with multiple criteria lookup.

**Table of Contents**hide

## Practice Workbook

You are well come to download the workbook from the link below.

## SUMPRODUCT with Multiple Criteria Lookup

### 1. OR logic Operation with SUMPRODUCT

We can use the **SUMPRODUCT** function as an **OR** logic operation. This means it will show the result if either of the criteria gets matched.

Before showing you any example let’s get to know some basics about the function.

**SUMPRODUCT** multiplies arrays (or ranges) together and returns the sum of products.

**SUMPRODUCT (array1, [array2], …)**

**array1:** The first array or range to multiply, then add.

**array2:** The second array or range to multiply, then add.

You can add as many arrays or ranges as you want. Apart from *array1, *other arrays are optional.

Feel free to visit the Microsoft Support site to know more about the **SUMPRODUCT** function.

Let’s see the examples that will help you understand the procedure.

From our table, we are aiming to find out the *Total Quantity *based on two criteria – *City *and *Product. *

Our **OR** logic operation can be done using a single **SUMPRODUCT** function or multiple **SUMPRODUCT** functions.

#### 1.1. Single SUMPRODUCT function

You might have known that ** '+'** works as

**OR**logic. We will use ‘

**to operate.**

`+`

‘While using a single **SUMPRODUCT **function we will separate the *array_criteria* by ‘** +‘**. And that will do the work for us.

Our formula will be

`SUMPRODUCT(((array_criteria1)+(array_criteria2)..)*sum_array)`

You need to write as many criteria as you want as array_criteria.

Here *array_critera* means criteria check inside the array.

Write the formula in Excel.

Here we have two criteria. City value can be found with *City* column (*A6 to A25*) and Product value with *Product* column (*C6 to C25*).

We have checked the criteria for the respective arrays and they were separated from each other.

Each of the array_criteria returns its own array of **TRUE **and **FALSE**.

Then the elements of those arrays get added together.

When two **TRUE **get added together the result will be **TRUE**

For two **FALSE, **it will be **FALSE**

And for one **TRUE **and **FALSE **each it will be **TRUE **

Then that array gets multiplied with the sum_range and stored as an array. Then the **SUMPRODUCT** function sums the elements within the array.

#### 1.2. Multiple SUMPRODUCT functions

When you are using multiple **SUMPRODUCT** functions all you need to do is use ‘`+`

‘ to between the **SUMPRODUCT** functions.

Our generic formula will be

`SUMPRODUCT((array_criteria1)*sum_array)+SUMPRODUCT((array_criteria2)*sum_array)`

This formula behaves similarly to the previous one, the only difference is here for each array_criteria we are picking a **SUMPRODUCT** function.

Write the formula in Excel.

For showing the similarity we have chosen the same example criteria here. It provides the same result as previous.

It’s a good practice to go with a single **SUMPRODUCT** function since that takes less amount time to execute the formula.

### 2. AND logic Operation with SUMPRODUCT

In our previous section, we have got the result while **SUMPRODCUT** behaves like **OR** operation, which provided result once either of the condition was satisfied.

If you need results that satisfy both(all) the criteria, then your desire operation should be **AND** operation. Let’s see how **AND** operation can be done using **SUMPRODUCT**.

Our generic formula is as follows

`SUMPRODUCT((array_criteria1)*(array_criteria2)*..*sum_array)`

Here `*`

operates as **AND** logic.

*array_criteria *returns an array of **TRUE **and **FALSE. TRUE** for match and **FALSE** for not match.

Then all these arrays get multiplied together. And forms another array. This array then gets multiplied with the *sum_range* array.

Then **SUMPRODUCT** function sums the element of the array and produces the result.

Let’s see an example.

Our selected criteria New York city and Backpack as Product. We aimed at finding the total number of *Backpack *in *New York* city.

First of all, we have checked for New York within the City column. For each match there returns a **TRUE**, **FALSE** otherwise. A similar procedure for product Backpack.

Then both the arrays get multiplied and form another array.

Multiplying two **TRUE** there forms **TRUE**, **FALSE** for rest.

This array then gets multiplied with the Present Quantity column and provides an array that of the elements that match the **TRUE** in the array.

Then we got the result after summing up the elements.

We can write the formula in another way. That approach is using unary (-) sing ahead of the *array_criteria*.

`SUMPRODUCT((--(array_criteria1))*(--(array_criteria2))*..*sum_array)`

These two unary operators (`--`

) convert the **TRUE** and **FALSE** into 1s and 0s. 1 for **TRUE** and 0 for **FALSE**.

The rest of the procedure remains the same as discussed previously.

Write the formula in excel.

It worked fine. We got the result we wanted. We have selected the same pair of criteria to show you the similarity between the formulas.

## SUMPRODUCT for a lookup value

You can use **SUMPRODUCT** to search for any value. But mere **SUMPRODUCT** will not do the task for you. You need to use the combination of **INDEX**, **SUMPRODUCT** and **ROW** functions.

*The INDEX function returns a value or the reference to a value from within a table or range.*

`INDEX(array, row_num, [column_num])`

**array: **A range of cells or an array constant where you want to search

**row_num**: The row in the array from which to return a value

**column_num**: The column in the array from which to return a value

If you want to know more about the **INDEX** function, please visit the Microsoft Support site.

*The ROW function returns the row number of a reference *

Let’s see our generic formula

`INDEX(search_array,SUMPRODUCT((array_criteria1)*(array_criteria2)*ROW(search_array)),0)`

Inside the **INDEX** function, we will provide the array from where we want to fetch the value.

The **SUMPRODUCT** function to find out the row where both criteria are met, and return the corresponding row number using the ROW function.

*0 is the column_number here. *

Let’s see an example.

We have selected seller Jay and quantity 88 as our criteria. Our intention is to find out the name of the city that matches the criteria.

Write the function Excel.

It gave the city name we were looking after. In our table, we have Jay with 88 quantities (regardless of the product) in New York city. We found that using the formula.

### An alternative (LOOKUP function)

The same task (fetching value) can be done by using the **LOOKUP** function.

Our formula will be something like this

`LOOKUP(lookup_value,1/(array_criteria1)/(array_criteria2),lookup_array)`

This formula divides 1 by an array of **TRUE/FALSE** values (*array_criteria1*), and then by another array of **TRUE/FALSE** values (*array_criteria2*) and so on. This will return either 1 or a ** #DIV/0! **error.

*lookup_value* tells the formula to match it with the numeric value in the range, where both conditions are **TRUE**

Write the formula in Excel.

We have checked the array criteria and divide 1 by that.

We have 2 as the lookup value, then the formula will match it with the last numeric value in the range, that is, the last row where both conditions are **TRUE**. This can be any number starting from 1.

We have got the result.

## Conclusion

That’s all for the article today. We have listed several ways to **SUMPRODUCT** with multiple criteria. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other formulas or methods which we might have missed here.