How to use VLOOKUP to sum multiple rows in Excel – 3 methods

Here are three easy methods!

The Microsoft Excel logo against a purple backdrop.

You can trust PC GuideOur team of experts use a combination of independent consumer research, in-depth testing where appropriate – which will be flagged as such, and market analysis when recommending products, software and services. Find out how we test here.

Last Updated on

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. 

A screenshot of an excel spreadsheet displaying a list of numbers.

What we want to accomplish: Explore the one way to use VLOOKUP and SUM to add multiple rows. 

  1. Using a helper column
  2. Using the SUMPRODUCT formula
  3. 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)

A screenshot of an Excel spreadsheet with multiple rows of numbers.

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:

A screenshot of an excel spreadsheet with a list of numbers used for SUM calculations.

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

A screenshot of an Excel spreadsheet with a list of numbers, used for calculating the sum.

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)

Learn how to create a table in Excel with just a few simple steps.

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:

A screenshot of an Excel spreadsheet with a list of numbers ready for performing a SUM calculation.

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”:

A screenshot of a Microsoft Excel spreadsheet showing a Sum function.

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: