=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. read more. 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 VLOOKUPVLOOKUPThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more and SUM function in excelSUM Function In ExcelThe SUM function in excel adds the numerical values in a range of cells. Being categorized under the Math and Trigonometry function, it is entered by typing “=SUM” followed by the values to be summed. The values supplied to the function can be numbers, cell references or ranges.read more 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 using VLOOKUP and SUM in Excel to find the total values that meet the criteria. Mainly four elements should be considered inside the LOOKUP function, including lookup_value, lookup_range, column_index, and logical_value.

The formula should be entered as follows: SUM(VLOOKUP(lookup_value, lookup_range, column_index, and logical_value))

  • lookup_value – This is the value we search for to determine the sum that matches exactly. It 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.column_index – To find, we should enter the sum of the array of indexes. One can enter all column indexes and a few column 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: The SUM function is also applied to the rows of matching values using the SUMPRODUCT in excelSUMPRODUCT In ExcelThe SUMPRODUCT excel function multiplies the numbers of two or more arrays and sums up the resulting products.read more and SUMIF function in excelSUMIF Function In ExcelThe SUMIF Excel function calculates the sum of a range of cells based on given criteria. The criteria can include dates, numbers, and text. For example, the formula “=SUMIF(B1:B5, “<=12”)” adds the values in the cell range B1:B5, which are less than or equal to 12. read more.

Top Examples of VLOOKUP with SUM Function

Below are some examples of VLOOKUP with the SUM function.

Example #1

Use of simple SUM and the VLOOKUP function.

In the present scenario, the VLOOKUP function eliminated individual calculation of the sum of sales produced.

Example #2

Determining the sum of sales generated in two different worksheets using Excel VLOOKUP and SUM. Consider the following data to explain this example.

Three worksheets are created, 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.

For determining 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.

Example #3

Summing of values presented in alternative columns

It is possible to sum the values shown 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 change the value in cell B14. It helps get the desired result. Therefore, it is the best feature provided by Excel VLOOKUP.

How to Use VLOOKUP with SUM Function?

We can also use VLOOKUP and SUM to do the following:

  • Determining the sum of matching values in columnsDetermining the sum of matching values in rows

The table is created for understanding, as shown in the figure below.

#1 – Determining the sum of matching values in columns

If we want to determine the total sales of the cooler from January to December, we should enter the specific criteria 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. Applying these to the Excel VLOOKUP and SUM formula will obtain the sales cooler’s total value. After entering a formula, press the “CTRL,” “SHIFT,” and “ENTER” keys at a time to yield the result. Otherwise, only the first cell’s value is displayed without all the values. This process applies 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 the cooler in the different rows. These are summed using the SUMPRODUCT function.

In this, the lookup_value is presented in cell A15, which 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 or 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 the lookup_value.We should create the name for a range of cells or arrays with VLOOKUP. It needs to ensure that the lookup value is placed in the first column for the proper working of VLOOKUP. For example, the cells’ range should begin with B when the lookup_value is presented in the cell with address C2.One thing to remember is that we must give index numbers to the columns to be easily used. So, for example, the lookup_value column is indicated with 1, the next column with 2, and so on.We should enter the appropriate logical value, 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 article is a guide to VLOOKUP with SUM in Excel. Here, we discuss using VLOOKUP with the SUM function, practical examples, and a downloadable Excel template. You may learn more about Excel from the following articles: –

  • Database in ExcelVlookup AlternativesMultiple Criteria of VLOOKUPIFERROR and VLOOKUP