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.

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.

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

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.

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

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

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

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

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

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

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

And press **F9** to see the insights

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.

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

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

To clarify the result, use the **SUMPRODUCT **function directly.

`=SUMPRODUCT(B4:E4,D15:G15)`

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

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

## Things to Remember

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

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

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

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

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.

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