# How to Use SUMPRODUCT to Lookup Multiple Criteria in Excel (2 Examples)

Below is a sales report dataset containing 4 columns: City, Seller Name, Product, and Quantity. We want to sum the quantity of the products according to multiple criteria.

### Method 1 – Apply Multiple Criteria with OR Logic Operation Using the SUMPRODUCT Function

#### 1.1. Using the Single SUMPRODUCT Function

Steps:

• Insert your criteria in cells G5 and H5.
• Click on cell I5 and insert the formula below:
`=SUMPRODUCT(((B5:B16=G5)+(D5:D16=H5))*E5:E16)`
• Press Enter.

As a result, you will get the total quantity for the products from New York or the product being Backpack.

#### 1.2. Using Multiple SUMPRODUCT Functions

Steps:

• Insert your required criteria in cells G5 and H5.
• Click on cell I5 and insert the formula below:
`=SUMPRODUCT((B5:B16=G5)*E5:E16)+SUMPRODUCT((D5:D16=H5)*E5:E16)`
• Press Enter.

As a result, two SUMPRODUCT functions will work together, and we will get our total quantity for products from New York or the backpack product.

### Method 2 – Apply Multiple Criteria with AND Logic Operation Using SUMPRODUCT Function

Steps:

• Click on cells G5 and H5 and insert your multiple criteria.
• Click on cell I5.
• Insert the following formula and press the Enter key.
`=SUMPRODUCT((B5:B16=G5)*(D5:D16=H5)*E5:E16)`

Thus, we will get the desired result for the New York City Backpackâ€™s total quantity.

Note: We can write the formula in another way. That approach is using two unary (-) operators ahead of the array_criteria. So, the formula would be: `=SUMPRODUCT((--(B5:B16=G5))*(--(D5:D16=H5))*E5:E16)`

These two unary operators (–) convert the TRUE and FALSE into 1s and 0s. 1 for TRUE and 0 for FALSE.

Related Content: Excel SUMPRODUCT Function Based on Date Range

## How to Use the SUMPRODUCT Function to Lookup a Value with Multiple Criteria in Excel

Steps:

• Insert the value Jay and quantity 100 on cells G5 and H5.
• Click on cell I5 and write the formula below:
`=INDEX(B5:B16,(SUMPRODUCT((C5:C16=G5)*(E5:E16=H5)*ROW(B5:B16))-4),0)`
• Press Enter.

Formula Breakdown:

=SUMPRODUCT((C5:C16=G5)*(E5:E16=H5)*ROW(B5:B16))-4

This returns the row number when the Seller’s name is Jay and the quantity is 100 and subtracts 4.

Result: 12

=INDEX(B5:B16,(SUMPRODUCT((C5:C16=G5)*(E5:E16=H5)*ROW(B5:B16))-4),0)

This returns the value for the previous resultant row of the B5:B16 range.

Result: Los Angeles

• Press Enter.

As a result, you will see the result for Jay in New York with a quantity of 88.

Read More: How to Use SUMPRODUCT IF in Excel

## The LOOKUP Function: A Better Alternative to SUMPRODUCT Function to Look Up a Value with Multiple Criteria

Steps:

• Insert your desired criteria in cells G5 and H5.
• Click on cell I5 and insert the following formula:
`=LOOKUP(2,1/(C5:C16=G5)/(E5:E16=H5),B5:B16)`
• Press Enter.

As a result, you can get the desired results in Los Angeles.

You can download our practice workbook from here.

## Related Articles

<< Go Back toÂ Excel SUMPRODUCT Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Shakil Ahmed

My nameâ€™s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF