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

When working with large datasets, sometimes it might occur to us that we need to sum specific cells of a column according to some specific criteria. You can achieve this complicated task by using the SUMPRODUCT function. In this article, I will show you 2 practical examples of the SUMPRODUCT function with multiple criteria lookup in Excel.

How to Use SUMPRODUCT to Lookup Multiple Criteria in Excel: 2 Examples

Say, we have a dataset of a sales report containing 4 columns named City, Seller Name, Product, and Quantity. Now, we want to sum the quantity of the products according to multiple criteria.

1. Apply Multiple Criteria with OR Logic Operation Using SUMPRODUCT Function

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.

Let’s say, you want to sum the quantity for the product being from New York City or the product being Backpack.

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

1.1. Using Single SUMPRODUCT Function

You might have known that ‘+’ works as OR logic. We will use ‘+’ to operate. So, while using a single SUMPRODUCT function we will separate the array_criteria by ‘+’. And that will do the work for us. Follow the steps below to achieve your desired result in this regard.

📌 Steps:

• First and foremost, insert your criteria in cells G5 and H5.
• Afterward, click on cell I5 and insert the formula below.

• Following, hit the Enter key.

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

When you are using multiple SUMPRODUCT functions all you need to do is use ‘+’ between the SUMPRODUCT functions. To do this, go through the steps below.

📌 Steps:

• First, insert your required criteria in cells G5 and H5.
• Afterward, click on cell I5 and insert the formula below.

• Subsequently, press the Enter key.

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

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

2. Apply Multiple Criteria with AND Logic Operation Using SUMPRODUCT Function

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

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

Say, we want to get the total quantity of product being from New York City and the product being Backpack. Follow the steps below to accomplish this.

📌 Steps:

• At the very beginning, click on cells G5 and H5 and insert your multiple criteria.
• Afterward, click on cell I5.
• Subsequently, insert the following formula and press the Enter key.

Thus, we will get our desired result of the New York City Backpack’s total quantity.

Related Content: Excel SUMPRODUCT Function Based on Date Range

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

You can also use the SUMPRODUCT function 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.

Say, you need to find the City for Jay when the quantity is 100. Follow the steps below to do this.

📌 Steps:

• First and foremost, insert the value Jay and quantity 100 on cells G5 and H5.
• Following, click on cell I5 and write the formula below.

• Subsequently, press the Enter key.

• Subsequently, hit the Enter key.

As a result, you will see the result as New York for Jay with quantity 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 in Excel

You can also accomplish this task with the LOOKUP function. Follow the steps below to do this.

📌 Steps:

• Initially, insert your desired criteria in cells G5 and H5.
• Afterward, click on cell I5 and insert the following formula.

• Subsequently., hit the Enter key.

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

Read More: Excel SUMPRODUCT with Multiple Criteria in Same Column

Download Practice Workbook

You can download our practice workbook from here for free!

Conclusion

So, in this article, I have shown you 2 practical examples of the SUMPRODUCT function with multiple criteria lookups in Excel. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. You are welcome to comment here if you have any further questions or recommendations.

Related Articles

• SUMPRODUCT Across Multiple Sheets in Excel
• SUMPRODUCT for Counting with Multiple Criteria in Excel
• [Solved] SUMPRODUCT with Multiple Criteria Not Working in Excel

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