In MS-Excel **VLOOKUP **is the most used important function to search any data from a dataset or table. In terms of calculation, sometimes we may need to get the summation of some searched data. In this way, there is a possible solution in Excel. Like we could simply use Excel’s **VLOOKUP **and **SUM **function to get the summation from multiple rows. In this article, we will see how we can use **VLOOKUP** and **SUM** functions to get summation from multiple rows.

**Table of Contents**hide

## Download the Practice Workbook

For method 1, 2, and 3

For method 4 only

**4 Ways to Use VLOOKUP and SUM for Multiple Rows**

**1. VLOOKUP And Sum All Matched Values in Multiple Rows**

Let’s assume we have a dataset of salespersons with their monthly sales. The table should be covered ID, Name, Months, and Sales. Now our task is to find out the total sales of a specific salesperson.

**Step 1:** Enter the following formula in cell **C16 **and press **Enter**

`=SUM(VLOOKUP(C15, $B$4:$G$11, {3,4,5,6}, FALSE))`

**Formula Explanation**

- Firstly, in
**VLOOKUP**function**C15**is containing our lookup value.**$B$4:$G$11**is the range where we will search for a lookup value.**{3,4,5,6}**this array is defining the row numbers of the table.**FALSE**is defining the exact match. - To explore more about the VLOOKUP function, you can check this link
- The
**SUM**function is used to get the total summation of the returned values of the**VLOOKUP**function. Like it will add the sales of the matched name. For more information about the**SUM**function. You can visit this link

*[Note:** If you are using OFFICE 365 then the formula will work this way. But if the OFFICE version is older or any other version, then you need to use this formula as an array formula. So instead of using only Enter, you will need to type CTRL + SHIFT + ENTER]*

**Step 2: **Enter any name in cell **C15 **and press **Enter**

**2. VLOOKUP and SUM to find the Sum of Matching Values in Multiple Rows from Different Worksheets**

Let’s assume the above scenario in two different worksheets. The first worksheet is named Sales Information and the second one is Total Sales.

**Read more:** VLOOKUP Formula in Excel with Multiple Sheets

Our task is to calculate total sales by each salesperson from the Sales information worksheet.

**Step 1: **Enter the following formula in cell **C4 **and copy it down up to **C11**

`=SUM(VLOOKUP(B4, 'Sales Information'!$B$4:$G$11, {3,4,5,6}, FALSE))`

**Formula Explanation**

- This formula is the same as the previous one. The only key difference is that we are defining the lookup value range as
**‘Sales Information’!$B$4:$G$11**where**‘Sales Information’**is the worksheet name.

**3. VLOOKUP and SUMIF to Look Up & Sum Values with Criteria**

In this section, we will find out the total maximum sales from the dataset. We will match if the searched Name has the maximum sales or not. If yes, then it prints “**Yes**” otherwise “**No**”.

**Step 1:** Enter the following formula in cell **C15 **and press **Enter**

`=IF(SUM(VLOOKUP(C14, $B$4:$G$11, {3,4,5,6}, FALSE))>=E15,"Yes","No")`

**Formula Explanation**

- In the
**IF**function**SUM(VLOOKUP(C14, $B$4:$G$11, {3,4,5,6}, FALSE))>=E15**is the logical condition. We are checking if the entered Name’s total sales are equal to greater than our predefined maximum sales or not. - If the sales match then we will print “Yes” otherwise “No”
- For more information about the
**IF**function, you can check this link

**Step 2: **Enter any name in cell **C15 **and press **Enter**

**4. Get Sum from Different Worksheets with VLOOKUP and SUM Function for Multiple Rows**

Now we will do the same thing as the first method but in a different way. Instead of having two tables in different worksheets, we are placing the table into two different workbooks. The files would be like this:

The table in the workbook will be the same as the previous one. Now the task is to get all the sales summation in the **Total Sales** workbook automatically.

**Step 1:** Open the **Total Sales** workbook and enter the following formula in cell **C4 **and copy it down up to **C11**

`=SUM(VLOOKUP(B4, '[Sales Information.xlsx]Sheet1'!$B$4:$G$11, {3,4,5,6}, FALSE))`

**Formula Explanation**

- This formula is the same as the previous formula, only the key difference is that here lookup range is like this
**‘[Sales Information.xlsx]Sheet1’!$B$4:$G$11** - Here
**[Sales Information.xlsx]**is the workbook filename **Sheet1**is meaning that in the workbook, the worksheet is named Sheet1.

**Step 2:** Now make a change in the **Sales Information** table and see the changes in the **Total Sales** workbook’s table.

**Changes in Sales Information Workbook**

**Changes in Total Sales Workbook**

**Observation:**

This formula is working automatically. That’s means if we made any changes in the source table, automatically the required changes will be done also in the destination table.

**Alternative Option**

In Excel, two functions can perform the **VLOOKUP **function, similarly to the **MATCH **and **INDEX **functions.

**INDEX MATCH And Sum All Matched Values in Multiple Rows**

Again, the dataset will be the same and we will calculate the sum of the entered name.

**Step 1: **Enter the following formula in cell **D13 **and press **Enter**

`=SUM(INDEX(C5:J9,,MATCH(D12,C4:J4,0)))`

**Formula Explanation**

- In the MATCH function, we are if the entered name in
**D12**is matched with any name from**C4**to**J9**range.**0**is used to get an exact match. To learn more about this function, you can visit this link - Then
**INDEX**function. We are selecting the index of the matched name from the whole range that**C5:J9**using the**INDEX**function. To explore more about the INDEX function, you can visit this link - Lastly, the
**SUM**function calculates the total of the selected index from the table.

**Step 2: **Now enter a name in cell **D12 **and press **Enter**

**Things to Remember**

Common Errors |
When they show |
---|---|

#NA in VLOOKUP/INDEX/MATCH |
If the searched value is not present in the given dataset, then all these functions will return this #NA error. |

#REF! | If col_index_num is greater than the number of columns in table-array, you’ll get the #REF! error value. |

#VALUE! | If the table_array is less than 1, you’ll get the #VALUE! error value. |

**Conclusion**

These are some ways to calculate the summation of multiple rows using **VLOOKUP **and **SUM **functions in Excel. I have shown all the methods with their respective examples but there can be many other iterations. I have also discussed the fundamentals of the used functions. If you have any other method of achieving this, then please feel free to share it with us.

JazzakAllahKhair Brother.