How to Use SUMPRODUCT to Lookup Multiple Criteria in Excel

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.


Download Practice Workbook

You can download our practice workbook from here for free!


2 Examples of Using SUMPRODUCT Function with Multiple Criteria Lookup in Excel

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.

Sample Dataset to Apply SUMPRODUCT with Multiple Criteria Lookup


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.
=SUMPRODUCT(((B5:B16=G5)+(D5:D16=H5))*E5:E16)
  • Following, hit the Enter key.

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.


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.
=SUMPRODUCT((B5:B16=G5)*E5:E16)+SUMPRODUCT((D5:D16=H5)*E5:E16)
  • Subsequently, press the Enter key.

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 product being Backpack.

Read More: [Solved] SUMPRODUCT with Multiple Criteria Not Working 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 condition 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.
=SUMPRODUCT((B5:B16=G5)*(D5:D16=H5)*E5:E16)

SUMPRODUCT with Multiple AND Criteria Lookup

Thus, we will get our desired result of 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.

Read More: SUMPRODUCT Multiple Criteria Same Column (3 Useful Ways)


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.
=INDEX(B5:B16,(SUMPRODUCT((C5:C16=G5)*(E5:E16=H5)*ROW(B5:B16))-4),0)
  • Subsequently, press the Enter key.

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 from it.

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 B5:B16 range.

Result: Los Angeles

  • Subsequently, hit the Enter key.

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

Read More: SUMPRODUCT Function with Multiple Columns in Excel (4 Simple Ways)


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.
=LOOKUP(2,1/(C5:C16=G5)/(E5:E16=H5),B5:B16)
  • Subsequently., hit the Enter key.

LOOKUP Function to Lookup Value

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


Conclusion

So, in this article, I have shown you 2 practical examples of the SUMPRODUCT function with multiple criteria lookup 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.

And, visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!


Further Readings

Shakil Ahmed

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

Leave a reply

ExcelDemy
Logo