Combine VLOOKUP with SUMIF

Yes, you can combine VLOOKUP and SUMIF. In SUMIF, there is a criteria argument in which you can use VLOOKUP to create a dynamic value. This will allow you to change the criteria by changing the lookup value in the VLOOKUP. The use of this combo formula is unique.

In this tutorial, we will learn to combine SUMIF and VLOOKUP to create a formula.

Combine SUMIF and VLOOKUP

1. First, in a cell enter “=SUMIF(“, for the range argument, refer to the product ID range that you have in table1.
2. After that, in the second augment, you need to use the VLOOKUP function to lookup for the product ID by using the product name from the cell above.
3. Next, in the third argument of the SUMIF, refer to the quantity column to use as a sum_range.
4. In the end, enter the closing parentheses and hit enter to get the result.

=SUMIF(A2:A13,VLOOKUP(B15,D1:E7,2,0),B2:B13)

How this Formula Works

Let’s break this formula into three parts: In the first part, you have specified the range where you have the product ID.

In the second part, you have the VLOOKUP that takes the product name from cell B15 and lookup it in the table2. For Headphones, we have the Product ID OT-356.

In the third part, we have the quantity column as sum_range.

In short, VLOOKUP helps you find the Product ID with the Product Name and then SUMIF takes that Product ID and looks it up in the Product ID column, and then sums values from the Quantity column.

As I said, when you use SUMIF and VLOOKUP, this makes your formula a dynamic formula. When you change the product name in the cells it changes the result.

SUMIF and VLOOKUP in Multiple Sheets

You can use this combination even when you have both tables in multiple sheets differently.

In the above example, you have the product name table on a different sheet.