FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

VLOOKUP With SUM

Updated on January 2, 2024
Article byDhanamma Pandula
Edited bySheeba M
Reviewed byDheeraj Vaidya, CFA, FRM

What Is VLOOKUP With SUM Function?

VLOOKUP, or Vertical Lookup, is a very versatile function that we can combine with other functions to get some desired result. VLOOKUP with SUM helps us calculate the sum of the numeric values based on the matching criterias or conditions.

To perform VLOOKUP with SUM, we combine two built-in Excel functions, i.e., SUM() and VLOOKUP(), to get the following syntax,

=SUM(VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).

For example, we will find the February bonus for an employee from the employee bonus data table given below.

Vlookup with sum Intro

Enter the formula =SUM(VLOOKUP(F2,$A$2:$D$7,{3,4,5},0)), and press “Ctrl+Shift+Enter” to execute the formula as an array.

Vlookup with sum Intro - Output

The total bonus earned by the employee “Sumit Bala” in 3 months is $10,870.

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.

The formula should be entered as follows: SUM(VLOOKUP(lookup_value, table_array, col_index_num, and range_lookup))

The four elements to be considered are,

  • lookup_value – It 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.
  • table_array – 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.
  • col_index_num – To find, we should enter the sum of the array of indexes. One can enter all or a few column indexes based on the requirement. These are helpful in the identification of the columns, including in sum.
  • range_lookup – Appropriate logic value, either 0 and 1 or “True” or “False” to select the values that match or approximately.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Download Template

This article must be helpful to understand the VLOOKUP with SUM Function, with its formula and examples. You can download the template here to use it instantly.

You can download this Vlookup with Sum Excel Template here – Vlookup with Sum Excel Template

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.

Vlookup with Sum Example 2

The sales of the laptop are determined using the SUM and VLOOKUP, which can also be done simply using the sum formulaSimply Using The Sum FormulaThe 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. The reason for using VLOOKUP with SUM is the feasibility of changing the lookup_value of cell G3 to determine sales of other items like “DVDs” and “Phones”. By changing the lookup_value to the phone, sales of the phone are generated, as shown below.

Vlookup with Sum Example 2-1

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.

Vlookup with Sum Example 3

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.

Vlookup with Sum Example 3-1

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.

Vlookup with Sum Example 4

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.

Vlookup with Sum Example 4-1

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, as shown below.

Vlookup with Sum Example 4-2

How To Use VLOOKUP With SUM Function?

We can use VLOOKUP With SUM function by,

  1. Determining the sum of matching values in columns.
  2. Determining the sum of matching values in rows.

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

Vlookup with Sum Example 1

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

Vlookup with Sum Example 1-1

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 the formula, press the “CTRL+SHIFT+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.

Vlookup with Sum Example 1-2

In this, the lookup_value is presented in cell A15, which is considered as a 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.

Important Things To Note

  • 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.  
  • Ensure to give the right index numbers to the columns to be easily used. 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, col_index_num, and range of cells to avoid errors.

Frequently Asked Questions

1. Where is the SUM function in Excel?

The SUM function in Excel is found as follows:
First, choose the cell range – select the “Formulas” tab – go to the “Function Library” group – click the “Maths & Trig” option drop-down – select the “SUM” function, as shown below.

Formulas - Sum Function

2. Where is the VLOOKUP function in Excel?

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.
The VLOOKUP function in Excel is found as follows:
First, choose the cell range – select the “Formulas” tab – go to the “Function Library” group – click the “Lookup & Reference” option drop-down – select the “VLOOKUP” function, as shown below.

Formulas - Vlookup

3. Why VLOOKUP with SUM is not working?

A few reasons VLOOKUP with SUM may not work are,
We have executed the formula with the keys “Ctrl+Enter” and have got the “#REF” error. We must remember to execute an array function with the keys “Ctrl+Shift+Enter”. However, VLOOKUP with SUM works without an array formula in the Office 365 version of Excel.
We have not given the Match value as 0 or 1 to get the exact or the approximate match.
We have entered an incorrect index number, due to which the column number is incorrect.

This article is a guide to VLOOKUP with SUM. Here we find lookup numeric values from Rows/Columns, determine their sum, examples & downloadable excel template. You may learn more about Excel from the following articles: –