0
VLOOKUP with Sum in Excel (Table of Contents)
VLOOKUP with Sum in Excel
Vlookup with Sum function in Excel is used to Sum the numbers from the looked up range if the selected range matches the lookup value. We can choose multiple columns from the selected table from where we want to Sum the values. For example, we have a table with sales data of fruits with the sales of different months in different columns. Then using Vlookup with Sum function will return the sum of any selected lookup cell which contains fruit name and this will sum the numbers from all the selected columns.
Vlookup in Excel Examples
Let’s look at some examples for vlookup with sum in excel.
“Vlookup with Sum in Excel – Example #1”>Vlookup with Sum in Excel – Example #1
We have a sales data of some products named as A, B, C,….H of 8 months. Where we need to see the total sale of any product in one shot. The data is shown below.
For that, we will be using Vlookup with Sum. First, we need to identify the criteria, which we want to see as output. Here I am considering to see the output in a cell below in the same table. For that go the edit mode in any of the cells where output will be printed. Now paste the formula =SUM(VLOOKUP(A2,’Sale Data’!$A$2:$I$9,{2,3,4,5,6,7,8,9},FALSE)) in edited cell.
And press Ctrl + Shift + Enter to see the output. Below screenshot shows the outcome of the applied formula.
Let’s do some experiment. For that instead of Ctrl + Shift + Enter, press only Enter to exit from the cell. As we can see in the below screenshot, by just pressing the Enter key, it will not give the summed output of entire selected row. Only the first value of that row will get reflected with this process.
The formula which is given above can be summarized by this simple logic.
=SUM(VLOOKUP(Lookup Value, Lookup Range, {2,3,4…}, FALSE))
- Lookup value is the fixed cell, for which we want to see sum.
- Lookup Range is the complete range or area of the data table, from where we want to look up the value. (Always fix the Lookup range, so that for other lookup value, the output will not get disturbed)
- {2,3,4… } are the column numbers, for which we need to see the sum of lookup cell.
- FALSE is the condition, which says we need to see exact results, we can use TRUE as well, which is used to see the nearly approximate result. This can be used when our data is in discrete form.
This is the main feature, which is why Microsoft enabled the actual output to be seen only by pressing Ctrl + Shift + Enter keys. It shows, when we correctly follow all the steps, then only we will be able to see actual output.
Vlookup with Sum in Excel – Example #2
Again, we will consider the same data as shown in below screenshot and see the output for all the product in one shot.
For this, identify a worksheet or place in the same sheet, where output needs to be seen. I have chosen a separate sheet in the same file to see the output. And put the same logical formula as we have seen above as =SUM(VLOOKUP(A2,’Sales Data’!$A$2:$I$9,{2,3,4,5,6,7,8,9},FALSE)) and press Ctrl + Shift + Enter. We will get the output as below screenshot.
As we can see, for all the product their summed sales value for 8 months are here. You can cross check as well if you want to compare the results.
Let’s do one more experiment and compare, what will be the difference in output if we press Ctrl + Shift + Enter and only Enter key.
Here we are having two output sets. And we can compare the data as well. By pressing only Enter key, the formula is printing only first column data of sales table, which is in column C. And by pressing Ctrl + Shift + Enter keys simultaneously, we are getting actually some of the sales of subsequent products.
Pros of Vlookup with Sum
- Once the formula is applied, even if we change the worksheet or excel file, the value will be the same and the results will not differ.
- Even if we have to press Ctrl + Shift + Enter to see the exact result, but the outcome will get frozen and it will allow us to have exact results.
Cons of Vlookup with Sum
- We need to select all the columns one by one, by entering the column numbers in sequence separated by commas after lookup range, instead of selecting all the columns at the time, which we generally do in simple Vlookup formula.
Things to Remember about the Vlookup with Sum in Excel
- Always freeze the lookup range, by pressing F4 key which will put $ sign in both sides. Which indicates that the cell or row or range is fixed. And the value will not get changed, even if we change the range or sheet or file location.
- Keep the track of columns which needs to be considered in summing the count, Sometimes, in the following, we may miss any number.
- Once we exit from the cell, the Vlookup with Sum formula itself gets enclosed in curve brackets {}, but if we wish to remove, it will again get hidden in the same cell and appear again when we exit from it. This is default function in Excel, which saves this formula.
- Always format the content before applying Vlookup with Sum, by doing this, it will remove unwanted characters, spaces and special characters as well to get the filtered sum value.
Recommended Articles
This has been a guide to VLOOKUP with Sum in Excel. VLOOKUP with Sum in Excel is a feature that automatically adjusts the width or height of a cell. Here we discuss how to use VLOOKUP with Sum in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
