Md. Abdullah Al Murad
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.
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))- 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.