If you want to know how to use VLOOKUP to sum multiple rows in Excel, we’ve got you covered.
VLOOKUP’s syntax only returns the first value in a column. However, if you have data where values are spread across multiple rows, using the simple SUM and VLOOKUP functions is only possible in one case. However, if you’re new to Excel, the entire process can seem challenging.
To help you out, we’re going to explain how to use VLOOKUP to sum multiple rows in Excel. We’ll go over three methods that can help get the job done.
Using VLOOKUP to sum multiple rows in Excel
Scenario on hand: We have a fictional dataset of store sales with customer number, products purchased, price, and quantity.
What we want to accomplish: Explore the one way to use VLOOKUP and SUM to add multiple rows.
- Using a helper column
- Using the SUMPRODUCT formula
- Using SUMIFS formula
Method 1: Using a helper column
Since customers purchased similar items, like Apple and Sugar, we need unique names for each entry. For this, we create a helper column.
Using a simple formula, we can add numbers in front of each product to create unique entries:
={Cell Address of lookup value}&COUNTIF({Cell Address of first entry with three $ signs},{Cell Address of lookup value})
=C6&COUNTIF($C$6:$C6,C6)
Note: In this formula, we add three $ signs instead of the regular four to create an absolute cell reference like this “$C$6:$C6”. This is intentional because we want to search for every entry across the column to create a unique name.
Now we expand this formula across the table to get unique names for every entry:
However, because the names are unique, we cannot apply the simple SUM and VLOOKUP formulas.
Instead, we use this formula:
=SUM(VLOOKUP(“{Product name}”&1,{Table array},{Column index number},FALSE),VLOOKUP(“{Product name}”&2,{Table array},{Column index number},FALSE))
=SUM(VLOOKUP(“Apples”&1,A6:E13,5,FALSE),VLOOKUP(“Apples”&2,A6:E13,5,FALSE))
Alternatively, you can also input the cell address of the product’s name instead of typing the name in the formula. Here’s the modified formula:
=SUM(VLOOKUP(G5&1,A6:E13,5,FALSE),VLOOKUP(G5&2,A6:E13,5,FALSE))
Alternate method 1: Using the SUMPRODUCT formula
Using SUMPRODUCT is the simplest way to sum across multiple rows.
Here’s the formula syntax:
=SUMPRODUCT(({Column array of product names}=”Apples”)*{Column array of quantity})
Here’s how we applied it to our data:
=SUMPRODUCT((C6:C13=”Apples”)*E6:E13)
We can also multiply this by the single unit price to get the $ sales amount and replace the product name in the formula with the cell address with the product name:
=SUMPRODUCT((C6:C13=G5)*E6:E13*D6)
Here:
- G5 is where “Apples” is written
- D6 is the per-unit price of apples
Here’s what we get after entering this formula:
Alternate method 2: Using SUMIFS
While VLOOKUP is a helpful formula, it is not ideally used for looking for the same value in the table and summing it up.
Here, we use the SUMIFS formula to add up the number of apples sold.
This is the formula syntax:
=SUMIFS({Column range of quantity column},{Column range of products column},{Cell address of lookup value})
For our data, here’s the formula we input:
=SUMIFS(E6:E13,C6:C13,G5)
Here’s the formula in action demonstrated for both “Apples” and “Sugar”:
Wrapping up
This was all about how to use VLOOKUP to sum multiple rows in Excel. This tutorial explored one primary method and two alternative methods to sum values across rows. The first one should work fine, but you can resort to alternate methods in case it does not work.
Learn more about the VLOOKUP function in Excel through these guides:
