SUMPRODUCT Multiple Criteria Lookup in Excel

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.

Excel sheet-SUMPRODUCT Multiple Criteria Lookup

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.

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.

Example - SUMPRODUCT Multiple Criteria Lookup

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)

SUMPRODUCT or- SUMPRODUCT Multiple Criteria Lookup

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.

Result - SUMPRODUCT Or- SUMPRODUCT Multiple Criteria Lookup

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)

SUMPRODUCT - s OR- SUMPRODUCT Multiple Criteria Lookup

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.

Result - SUMPRODUCT - SUMPRODUCT Multiple Criteria Lookup

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)

SUMPRODUCT AND - SUMPRODUCT Multiple Criteria Lookup

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.

SUMPRODUCT AND

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)

Unary AND

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.

Unary AND

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)

SUMPRODUCT lookup-SUMPRODUCT Multiple Criteria Lookup

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.

Example SUMPRODUCT look

Write the function Excel.

Sumproduct lookup

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)

LOOKUP

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.

LOOKUp result

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.


Further Readings

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