You can nest VLOOKUP inside SUMIF’s criteria argument when the value you need to sum by isn’t the one you have. The formula is =SUMIF(A2:A13,VLOOKUP(B15,D2:E7,2,0),B2:B13): VLOOKUP takes the product name in B15, returns its product ID from the reference table, and SUMIF then totals the quantity column for every row carrying that ID.
So a user picks a readable name — ideally from a dropdown in B15 — while the data itself stores codes. The same works across sheets by adding the sheet name to the lookup range: =SUMIF(A2:A13,VLOOKUP(B15,Sheet2!A2:B7,2,0),B2:B13).
Two things to guard against. If VLOOKUP can’t find the name it returns #N/A, and SUMIF passes that straight through instead of returning 0 — wrap it as =IFNA(SUMIF(...),0).
And the ID VLOOKUP returns must match the ID column exactly: a trailing space or a code stored as text on one side gives you 0 with no error.
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
- First, in a cell, enter “=SUMIF(“, and for the range argument, refer to the product ID range that you have in table1.
- After that, in the second argument, you need to use the VLOOKUP function to look up the product ID by using the product name from the cell above.
- Next, in the third argument of the SUMIF, refer to the quantity column to use as a sum_range.
- In the end, enter the closing parenthesis 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 looks it up in 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 together, it makes your formula a dynamic formula. When you change the product name in the cell, it changes the result.

SUMIF and VLOOKUP in Multiple Sheets
You can use this combination even when you have the two tables on different sheets.

In the above example, you have the product name table on a different sheet.
In that case, add the sheet name in front of the lookup table in VLOOKUP. For example, if the product table is in A1:B7 of a sheet named Products, the formula would be:
=SUMIF(A2:A13,VLOOKUP(B15,Products!A1:B7,2,0),B2:B13)
If the sheet name has spaces, put it in single quotes, like ‘Product List’!A1:B7.
Handle the #N/A Error
If the product name in B15 isn’t in the product table, VLOOKUP returns #N/A, and the whole formula returns #N/A too. To show 0 instead, wrap the formula in IFERROR:
=IFERROR(SUMIF(A2:A13,VLOOKUP(B15,D1:E7,2,0),B2:B13),0)
Before you hide the error, check for the usual causes: a typo in the product name, extra spaces, or a name that is missing from the table.
Copy the Formula to More Products
If you want to get the totals for a list of product names and copy the formula down, lock the ranges with dollar signs so they don’t move. Only the cell with the product name should change:
=SUMIF($A$2:$A$13,VLOOKUP(B15,$D$1:$E$7,2,0),$B$2:$B$13)
Use INDEX MATCH or XLOOKUP Instead of VLOOKUP
VLOOKUP only works when the product name is in the first column of the lookup table. If the product ID column comes before the name, use INDEX and MATCH instead:
=SUMIF(A2:A13,INDEX(E1:E7,MATCH(B15,D1:D7,0)),B2:B13)
And in Excel for Microsoft 365 and Excel 2021 and later, you can use XLOOKUP, which also lets you set a value for when the name isn’t found:
=SUMIF(A2:A13,XLOOKUP(B15,D1:D7,E1:E7,""),B2:B13)
Frequently Asked Questions
Can I use SUMIFS with VLOOKUP?
Yes. Put the VLOOKUP in any criteria argument of SUMIFS, and add more conditions, like a date or a region, in the other criteria pairs.
What if the product name appears twice in the lookup table?
VLOOKUP returns the ID from the first match only, so the formula sums the quantities for that one ID. Keep the names in the lookup table unique.
Why do I need 0 at the end of VLOOKUP?
The 0 (or FALSE) tells VLOOKUP to find an exact match. Without it, VLOOKUP uses an approximate match and can return the ID of a different product.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.