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

Posted on by Shakil Ahmed

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.

Sample Dataset to Apply SUMPRODUCT with Multiple Criteria Lookup

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.

Using SUMPRODUCT with Multiple OR Criteria Lookup

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

Read More: How to Use SUMPRODUCT with Criteria in Excel

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.

Multiple SUMPRODUCT with Multiple OR Logic

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.

Read More: How to Use SUMPRODUCT Function with Multiple Columns in Excel

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)

SUMPRODUCT with Multiple AND Criteria Lookup

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.

SUMPRODUCT Function to Lookup a Value

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.

LOOKUP Function to Lookup Value

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

Read More: Excel SUMPRODUCT with Multiple Criteria in Same Column

Download the Practice Workbook

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!

Leave a Reply

Your email address will not be published. Required fields are marked *

Advanced Excel Exercises with Solutions PDF