Combining SUMPRODUCT and VLOOKUP in Excel (2 Examples)

In Excel, you may need to use different sum family functions: SUM, SUMIF, SUMPRODUCT, and so on. Depending on circumstances, it’s quite common to pair these functions with a lookup function like LOOKUP, VLOOKUP, or HLOOKUP.

Today we are going to show you how to use the SUMPRODUCT and VLOOKUP functions together. Before jumping into the tutorial, let’s get to know about the workbook which is the base of our examples.

Dataset - SUMPRODUCT VLOOKUP

Here we have a table, consists of several products with their revenues over five months. We will use this dataset to find the total (sum) while matching criteria.

Note that this is a basic table with dummy data to keep things simple. In a real-life scenario, you may encounter a much more complex and larger dataset.

Practice Workbook

You are welcome to download the practice workbook from the link below.

Examples with SUMPRODUCT VLOOKUP Combination

Just for reminder, SUMPRODUCT multiplies the arrays together and returns the sum of the product. And VLOOKUP search values in a table by row or vertically. You can check out the following articles: SUMPRODUCT, VLOOKUP.

1. Calculate Total Revenue (Dealing with Criteria)

From our example dataset, we will find the total revenue for a given product.

Criteria - SUMPRODUCT VLOOKUP

Here we will set the product name as the criteria value. For example, we have set Desktop as the criteria.

Criteria value - SUMPRODUCT VLOOKUP

Now our formula will be the following one

=SUMPRODUCT(VLOOKUP(D13,B4:G10,{2,3,4,5,6},0))

Within the SUMPRODUCT function, we have written VLOOKUP to find the arrays.

Formula for Example 1 - SUMPRODUCT VLOOKUP

D13 is the lookup_value and the B4:G10 is the lookup_array. You can see, we have inserted 2 to 6 within curly braces. It is in the column_num field.

Since we need the values from column 2 (C) to column 6 (G) that’s why we have set them in such a way. And 0 is for the exact match.

This provides an array to SUMPRODUCT. And the function adds the elements of the array and returns the result.

Formula result - SUMPRODUCT VLOOKUP

We have found the total revenue for the product Desktop. To clarify the result, SUM the revenue from the Desktop row.

SUM result - SUMPRODUCT VLOOKUP

You should get the same amount as our SUMPRODUCT-VLOOKUP combination provided.

SUM and SUMPRODUCT Result - SUMPRODUCT VLOOKUP

2. Calculate Final Grade

We can calculate the values for multiple arrays using the SUMPRODUCT – VLOOKUP combination.

In this section, we will calculate the final grade for several students.

Example 2 dataset - SUMPRODUCT VLOOKUP

Here our calculation criteria will be in such a way that depending on the score the weight of the score will be different.

Weight table - SUMPRODUCT VLOOKUP

Here when a student scored lowest in Quiz 1 then the weight will be 0.15, 0.25, 0.2, and 0.4 for Quiz 1, Quiz 2, Assignment, and Final Quiz respectively.

We have listed the weight value for each case in the table (below the score table)

We will achieve the final grade by multiplying a weight value to the score and then summing them. For that our formula is the one below

=SUMPRODUCT(B4:E4,VLOOKUP(MATCH(MIN(B4:E4),B4:E4,0),$C$14:$G$17,{2,3,4,5},0))

Here we have inserted the first array of SUMPRODUCT as B4:E4, which is the scores for the first student.  

Formula for example 2 - SUMPRODUCT VLOOKUP

And for the second array, we have used the VLOOKUP function. This will help us find the weight value as array perfectly since we don’t know which of the weight value should be applicable for the respective score.

Here we have used the MATCH and MIN functions. The MATCH function locates the position of a lookup value in a row, column, or table. And MIN returns the lowest value from a given range of numbers. Visit these MATCH, MIN articles for further information.

The MATCH portion provides the column number for the lowest value within the range. Select that portion

MATCH insights - SUMPRODUCT VLOOKUP

And press F9 to see the insights

MATCH insights 2 - SUMPRODUCT VLOOKUP

Here we have found 2 from the MATCH portion (will change depending on minimum value). This will be the lookup_value for VLOOKUP. And the weighted table is the lookup_array.

This will derive the correct weight value as an array.

VLOOKUP insights - SUMPRODUCT VLOOKUP

Select the entire VLOOKUP portion and press F9, you will get the array

VLOOKUP Insights - SUMPRODUCT VLOOKUP

Then the SUMPRODUCT will multiply the elements of the two arrays and then add them. Here we have found the result.

Formula for example 2 result - SUMPRODUCT VLOOKUP

To clarify the result, use the SUMPRODUCT function directly.

=SUMPRODUCT(B4:E4,D15:G15)

Direct use of SUMPRODUCT - SUMPRODUCT VLOOKUP

Here we have directly set the scores and weight value as the arrays. You should get the same value as the SUMPRODUCT-VLOOKUP returns.

SUMPRODUCT result - SUMPRODUCT VLOOKUP

Write the formula for the rest of the values or exercise the AutoFill feature.

AutoFill for example 2 - SUMPRODUCT VLOOKUP

Things to Remember

  1. For our first example, where we have found the total revenue, we can use the SUM function instead of SUMPRODUCT.

SUM function in formula - SUMPRODUCT VLOOKUP

But if we mere use ENTER it will not provide the right result.

Error in result - SUMPRODUCT VLOOKUP

Since we are dealing with an array, we need to use CTRL + SHIFT + ENTER then.

Result of SUM formula - SUMPRODUCT VLOOKUP

  1. Make sure to insert the lookup_array in such a way that the lookup_value can be found in the first column.

Select range - SUMPRODUCT VLOOKUP

Here we have set the lookup_array from the B14 column, but this column doesn’t contain the lookup_value. That’s we will find the #N/A error.

Not available error - SUMPRODUCT VLOOKUP

  1. Write the column number correctly so that we can get the right array to multiply.

Conclusion

That’s all for today. We have shown you how to use the SUMPRODUCT and VLOOKUP together. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other scenarios to utilize these functions together.

shakil

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