Combining SUMPRODUCT and VLOOKUP Functions in Excel

Get FREE Advanced Excel Exercises with Solutions!

In Excel, you may need to use different sum family functions: SUM, SUMIF, SUMPRODUCT, and so on. Depending on the 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.


Combine SUMPRODUCT and VLOOKUP Functions: 2 Suitable Examples

Just as a reminder, SUMPRODUCT multiplies the arrays together and returns the sum of the product. And VLOOKUP search values in a table by row or vertically. Before jumping into the tutorial, let’s get to know the workbook, which is the basis of our examples. Here we have a table, consisting of several products with their revenues over five months. We will use this dataset to find the total (sum) while matching criteria.

sumproduct vlookup

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.


Example 1: Combine SUMPRODUCT and VLOOKUP Functions for Multiple Criteria to Calculate Total Revenue

From our example dataset, we will find the total revenue for a given specific product. Here we will set the product name as the criteria value. For instance, we have set Desktop as the criteria. Let’s follow the instructions below to learn!

Steps:

  • First of all, type the following formula in cell E14.
=SUMPRODUCT(VLOOKUP(E13,B5:G11,{2,3,4,5,6},0))
  • After that, hit Enter to get the return of the functions.
  • Hence, we will get the return, which is $940000.

Combine SUMPRODUCT and VLOOKUP Functions for Multiple Criteria to Calculate Total Revenue

Formula Breakdown
  • Inside the VLOOKUP function, E13 is the lookup_value and the B5:G11 is the lookup_array. You can see, we have inserted 2 to 6 within curly braces. It is in the column_num
  • 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.
  • Then, inside the SUMPRODUCT function, VLOOKUP(E13,B5:G11,{2,3,4,5,6},0) adds the elements of the array and returns the result.
  • Finally, we have found the total revenue for the product Desktop.
  • In the above discussion, we have found the total revenue. Now, we can use the SUM function instead of the SUMPRODUCT function.
  • Afterward, to clarify the result, SUM the revenue from the Desktop.
  • You should get the same amount as our SUMPRODUCT-VLOOKUP combination provided.

  • In the above discussion, we used the SUMPRODUCT and VLOOKUP functions to calculate the total revenue of a specific product. We can calculate the total revenue of a specific product using the SUM function instead of SUMPRODUCT The formula will be,
=SUM(VLOOKUP(E13,B5:G11,{2,3,4,5,6},0))

Formula Breakdown
  • Inside the VLOOKUP function, E13 is the lookup_value and the B5:G11 is the lookup_array. You can see, we have inserted 2 to 6 within curly braces. It is in the column_num field, and 0 is used for the exact match.
  • Finally, the SUM function calculates the total revenue earned from the product named Desktop.
Notes
  • Here we have set the lookup_array from the B5 column, but this column doesn’t contain the lookup_value. That’s we will find the #N/A
  • We will get the #VALUE! Error while using the SUMPRODUCT function if there is text in one or more of the reference range’s cells.

Read More: How to Use VLOOKUP Function with INDIRECT Function in Excel


Example 2: Calculate Final Grade Using SUMPRODUCT and VLOOKUP Combination

Last but not least, we can calculate the values for multiple arrays using the SUMPRODUCT and VLOOKUP combinations. In this section, we will calculate the final grade for several students.

Here, our calculation criteria will be such that depending on the score, the weight of the score will be different. Here, if 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.

After that, we have listed the weight value for each case in the table (below the score table)

Calculate Final Grade Using SUMPRODUCT and VLOOKUP Combination

Steps:

  • We will achieve the final grade by multiplying a weight value by the score and then summing them. For that, our formula is the one below.
=SUMPRODUCT(C5:F5,VLOOKUP(MATCH(MIN(C5:F5),C5:F5,0),$C$14:$G$17,{2,3,4,5},0))
  • Hence, simply press Enter on your keyboard. As a result, you will get the return which is the return of the functions. The return is 83.05.

Calculate Final Grade Using SUMPRODUCT and VLOOKUP Combination

Formula Breakdown
  • Here we have inserted the first array of SUMPRODUCT as C5:F5, which is the score for the first student.
  • And for the second array, we have used the VLOOKUP This will help us find the weight value as an array perfectly since we don’t know which of the weight values should be applicable for the respective score.
  • Here we have used the MATCH and MIN 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.
  • MATCH(MIN(C5:F5),C5:F5,0) provides the column number for the lowest value within the range.
  • And press F9 to see the insights. Here we have found 2 from the MATCH portion (will change depending on minimum value). MATCH(MIN(C5:F5),C5:F5,0) will be the lookup_value for the VLOOKUP, and $C$14:$G$17 is the weighted table range which is the lookup_array, and 0 is used for the exact match.
  • Afterward, write the formula for the rest of the values or exercise the AutoFill feature.

  • Afterward, to clarify the result, use the SUMPRODUCT function directly. The SUMPRODUCT function is,
=SUMPRODUCT(C5:F5,$D$15:$G$15)
  • Where C5:F5 is the array1, and $D$15:$G$15 is the array2 of the SUMPRODUCT We use the Dollar sign($) for absolute reference.

Calculate Final Grade Using SUMPRODUCT and VLOOKUP Combination

Here we have directly set the scores and weight values as the arrays. It would be best if you got the same value as the SUMPRODUCT-VLOOKUP returns.

Read More: 7 Practical Examples of VLOOKUP Function in Excel


Download Practice Workbook

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


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 of any other scenarios to utilize these functions together.


Further Readings


<< Go Back to Excel VLOOKUP Sum | Excel VLOOKUP Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo