Vlookup is a very versatile function which can be combined with other functions to get some desired result, one such situation is to calculate the sum of the data ( in numbers) based on the matching values, in such situations we can combine sum function with vlookup function, the method is as follows =SUM(Vlookup(reference value, table array, index number, match).
Vlookup with SUM Function
VLOOKUP is one of the advanced features of ExcelAdvanced Features Of ExcelThe top features of MS excel are - Shortcut keys, Summation of values, Data filtration, Paste special, Insert random numbers, Goal seek analysis tool, Insert serial numbers etc. . It is used as the database function to perform calculations on the tables imported from the database. In the present article, the combined use of VLOOKUP and SUM function in excel to determine the specific criteria and find the sum of the values presented in all the VLOOKUPs.
Explanation of VLOOKUP with Sum in Excel
We need to understand the various terms associated with the use of VLOOKUP and SUM in excel to find the total of values that meet the criteria. Mainly four elements should be considered inside the lookup function, including lookup value, lookup range, indexes of columns, and logical value.
The formula should be entered as = SUM(VLOOKUP(lookup value, lookup range, column index, and logical value))
- Lookup Value – This is the value we search for determining the sum that matches exactly. It definitely changes the lookup value to determine the sum of different columns using different criteria.
- Lookup Range – It is the range of cells helpful to search for data using the specified criteria. Generally, it will be a table of data generated from different sources.
- Indexes of Columns – To find the sum of the array of indexes should be entered. One can enter all column indexes and a few columns indexes based on the requirement. These are helpful in the identification of the columns, including in sum.
- Logical Value – Appropriate logic value either 0 and 1 or true or false to select the values that match or approximately
Note: Sum function is also applied to the rows of matching values using the SUMPRODUCT in excelSUMPRODUCT In ExcelThe SUMPRODUCT function in excel is used to multiply the array or range and provide the sum of a product. It is to calculate a weighted average. SUMPRODUCT formula is used to calculate the sum of corresponding numbers product in one or more ranges/arrays. and SUMIF function in excelSUMIF Function In ExcelThe SUMIF Function is a conditional sum function that calculates the sum of given numbers based on a condition. It only uses one condition, and the syntax for using this function is =SUMIF (Range, Criteria, Sum range)..
Top Examples of VLOOKUP with SUM Function
Below are some examples of VLOOKUP with SUM Function.
Use of simple sum and vlookup function
The sales of the laptop are determined using the sum and vlookup. But, this can be simply using the sum formula also. The reason for using vlookup with sum is that feasibility in changing the lookup values of cell G3 to determine sales of other items like DVD and phone. By changing the lookup value to the phone, sales of the phone are generated, as shown in the below screenshot.
In the present scenario, the VLOOKUP function eliminated individual calculation of the sum of sales produced.
Determining the sum of sales generated in two different worksheets using Excel VLOOKUP and SUM. The following data is considered to explain this example.
Three worksheets are creating, including Jan, Feb, and summary, to determine the total sales generated by a customer. In this, two vlookup functions are added for two worksheets January and February, to determine the total sales as shown in the below-mentioned figure.
To determine the sales generated by other customers, the formula is dragged to other rows by adding absolute cell reference to the range of data. It will produce accurate results in the use of sum and vlookup.
Summing of values presented in alternative columns
It is possible to sum the values presented in alternative and specified columns. The following table is considered to illustrate the present example.
Suppose we want to determine the sum of values in alternative columns, including January, March, May, July, September, and November. To do this, indexes of only these columns should be considered instead of all column indexes, as shown in the below screenshot.
To get sales of other products, simply changing the value in the cell B14 helps in getting the desired result. This is the best feature provided by the Excel VLOOKUP.
How to Use VLOOKUP with SUM Function?
VLOOKUP and SUM can also be used to do the following –
- Determining the sum of matching values in columns
- Determining the sum of matching values in rows
For understanding, the table is created, as shown in the below figure.
#1 – Determining the sum of matching values in columns
If we want to determine total sales of the cooler from January to December, the specific criteria should be entered into the empty cell
In this, the lookup value is presented in the cell called A15 and A2:M11 is the range of data, the numbers 2 to 13 are the indexes for the columns. By applying these into the excel vlookup and sum formula, the total value of sales cooler will be obtained. After entering a formula, CTRL, SHIFT, and ENTER at a time to yield the result; otherwise, only the value of the first cell is displayed without all the values. This process is applicable to the remaining products to find the sum of values in different columns.
#2 – Determining the sum of matching values in rows
The below-mentioned sheet shows the sales of cooler in the different rows. These are summed using the SUMPRODUCT function.
In this, the lookup value is presented in the cell A15 that is considered as criteria to determine the sum of sales. We can change this value to other products to find out the sales. But, in this process, simply pressing of entering after writing the formula yields better results.
Things to Remember about Excel Vlookup with Sum
It needs to remember the following things while using the VLOOKUP function by developing appropriate syntax:
- The value we wish to lookup is termed as the lookup value.
- The name should be created for a range of cells or array to use with VLOOKUP. It needs to ensure that the lookup value is placed in the first column to the proper working of VLookup. For example, the range of cells should be beginning with B when the lookup value is presented in the cell with address C2.
- One thing to remember that index numbers have to be given to the columns to easily use. The lookup value column is indicated with 1, the next column with 2, and so on.
- The appropriate logical value should be entered either true or false, to the approximate or exact match of a lookup value, index number, and range of cells. Otherwise, errors will occur.
This has been a guide to VLOOKUP with SUM in Excel. Here we discuss how to use VLOOKUP with SUM Function along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –