# How to Use VLOOKUP to Sum Multiple Rows in Excel (4 Methods)

In the dataset below, we have two columns displaying Products and Sales.

### Method 1 – Using VLOOKUP and Sum Matched Values in Multiple Rows

Steps:

• Enter the following formula in cell B5 to create the Helper Column.

`=C5&COUNTIF(\$C\$5:\$C5,C5)`

• Click Enter and use the AutoFill tool to the whole column.

• Select cell D12 and enter the following formula:

`=SUM(VLOOKUP("Apple"&1,B5:D10,3,FALSE),VLOOKUP("Apple"&2,B5:D10,3,FALSE))`

Formula Breakdown:

• The VLOOKUP function matches the criteria Apple with ranges B5:D10 from the dataset.
• Here, you have to search twice, as the Helper column shows Apple twice.
• The VLOOKUP function extracts the values of the matched cells.
• The SUM function provides the sum of the output values provided by the VLOOKUP function.
• Hit Enter to get the total sales of Apples.

Read More: How to Use VLOOKUP for Rows in Excel

### Method 2 – Inserting the SUMPRODUCT Function to VLOOKUP and Sum

Steps:

• Select cell C12 and enter the following formula:

`=SUMPRODUCT((B5:B10="Apple")*C5:C10)`

• Press Enter to get a similar output.

### Method 3 – Using VLOOKUP and Sum Multiple Rows from Different Worksheets

Steps:

• Click on cell C5 and enter the formula below:

`=VLOOKUP(B5, Dataset!B5:C10, {2}, FALSE)`

Note: In the formula, {2} indicates the column index number. It starts from 1, which indicates the first column in an Excel sheet. Here, our dataset starts from the second column, so we used 2 as the column index.

• Apply the AutoFill tool to the entire column of the dataset.

• Select cell C12.
• Enter the following formula:

`=SUM(C5:C10)`

• Press the Enter button to get the result.

### Method 4 – Using VLOOKUP and SUMIF Multiple Rows with Criteria

Steps:

• Select cell D13 and enter the formula below:

`=MAX(SUM(C5:D5),SUM(C6:D6),SUM(C7:D7),SUM(C8:D8),SUM(C9:D9),SUM(C10:D10))`

• Press the Enter button to find the Maximum Sales.

• Enter the formula below in cell D14:

`=IF(SUM(VLOOKUP(D12, \$B\$5:\$D\$10,{2,3}, FALSE))>=D13,"Yes","No")`

Formula Breakdown

• In the IF function, SUM(VLOOKUP(D12, \$B\$5:\$D\$10, {2,3}, FALSE))>=D13 is the logical condition.
• {2,3} is an argument of the VLOOKUP function, which indicates the column index number. Usually, it starts from 1, which indicates the first column in an Excel sheet.
• However, the VLOOKUP function checks if the entered Name’s total sales are greater than or equal to our predefined maximum sales or not. Here, it searched for Luka Howard and provided the amount of sales for January and February.
• The SUM function provides the sum of the sales amount received from the VLOOKUP function. However, the output is 1350\$.
• The IF function checks the condition. Here, the maximum sale is given as 1595\$, and the formula will check whether the output we received is maximum or not. If the sales match, it will print “Yes”; otherwise, “No.”
• Hit the Enter key to get the final result.

Read More: INDEX MATCH vs VLOOKUP Function

## How to Sum Multiple Rows with INDEX and MATCH Functions

Here, we will calculate the total sales of employee Ethan Scott for various months of the year. For the purpose of demonstration, the dataset has been changed.

Steps:

• Enter the following formula in cell D12:

`=SUM(INDEX(C5:D10,,MATCH("Ethan Scott",C5:D5,0)))`

• Press Enter to calculate the total sales of Ethan Scott.

## Things to Remember

• If the searched value is absent in the given dataset, all these functions will return this #NA Error.
• If col_index_num is greater than the number of columns in the table array, you’ll get the #REF! Error value.
• You’ll get the #VALUE! Error value If the table_array is less than 1.

<< Go Back to Excel VLOOKUP Sum | Excel VLOOKUP Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Md. Abdullah Al Murad is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find... Read Full Bio

1. JazzakAllahKhair Brother.

2. Hey, man. It looks like you are getting the sum of the columns (not the rows as indicated) in the first example.

3. what {2,3} mean?