VLOOKUP SUM Multiple Rows (4 Ways with Alternative)

VLOOKUP SUM Multiple Rows

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.

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.

VLOOKUP And Sum All Matched Values in Multiple Rows

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

Enter formula using SUM and VLOOKUP functions

[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

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

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

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.

Enter formula using SUM and VLOOKUP

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

VLOOKUP and SUMIF to Look Up & Sum Values with Criteria

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

Enter formula using IF SUM and VLOOKUP functions

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

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:

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

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.

Enter formula using SUM and VLOOKUP

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 Sales Information Workbook

Changes in Total Sales 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.

INDEX MATCH And Sum All Matched Values in Multiple Rows

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.

Enter formula using SUM MATCH and INDEX functions

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

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.


Further Readings:

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

1 Comment
  1. JazzakAllahKhair Brother.

Leave a reply

ExcelDemy
Logo