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.

**Table of Contents**Expand

## 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.

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.

**Formula Breakdown**- Inside
**the VLOOKUP function**,**E13**is theand the*lookup_value***B5:G11**is the. You can see, we have inserted 2 to 6 within curly braces. It is in the*lookup_array**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 theand the*lookup_value***B5:G11**is the. You can see, we have inserted*lookup_array***2**to**6**within**curly braces**. It is in thefield, and*column_num***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. Thatâ€™s we will find the*lookup_value***#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)

**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.

**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 thefor the*lookup_value***VLOOKUP,**and**$C$14:$G$17**is the weighted table range which is the, and*lookup_array***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**.

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

- How to Use VLOOKUP with COUNTIF
- How to Combine SUMIF and VLOOKUP in ExcelÂ
- Use VLOOKUP to Sum Multiple Rows in Excel
- INDEX MATCH vs VLOOKUP Function
- Excel LOOKUP vs VLOOKUP
- XLOOKUP vs VLOOKUP in Excel
- How to Use Nested VLOOKUP in Excel
- IF and VLOOKUP Nested Function in ExcelÂ
- How to Use IF ISNA Function with VLOOKUP in Excel
- How to VLOOKUP and SUM Across Multiple Sheets in Excel
- How to Use IFERROR with VLOOKUP in Excel
- VLOOKUP with IF Condition in Excel

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