## What Is VLOOKUP With SUM?

VLOOKUPfunction is useful whenever we want to retrieve values from a large data set based on certain values. It is often used to retrieve the scalar value or single value of the desired lookup value. However,VLOOKUPwithSUMadds the sum of all the desired lookup values as an array function, to get the eventual result.

For instance, we have the following monthly product sales data in an Excel spreadsheet. In cell **I2,** we will find the total units sold for the product “**Groceries**”.

Select cell **I2**, enter the formula **=SUM(VLOOKUP(H2,$A$2:$F$6,{2,3,4,5,6},0)),** and press the keys “**Ctrl + Shift + Enter**”, as this is an array formula in excel.

We get the output as **1559**, for the lookup value of “**Groceries**”, as shown above.

##### Table of contents

###### Key Takeaways

- In
**VLOOKUP with SUM,****VLOOKUP**will return multiple column values, and then the**SUM function**will add up those numbers to get the sum of multiple columns. **VLOOKUP**can fetch the value from more than one column if we specify the column numbers in curly brackets.**VLOOKUP with SUM**works without an array formula in the Office 365 version of Excel.- When we need to get the sum from multiple rows,
**VLOOKUP**cannot fetch that. In such scenarios, we use the**SUMPRODUCT function**to match the criteria and get the sum from the matching rows. - Inside the
**SUMIF function**, we can use the**VLOOKUP function**to get the criteria to value from a different table.

### How To Use VLOOKUP With SUM?

Since Excel doesn’t have an inbuilt function for **VLOOKUP With SUM**, we can combine the **VLOOKUP **and the **SUM excel function**.

The syntax of the combined** VLOOKUP formula** with the **SUM formula** is as follows,

*=SUM(VLOOKUP(lookup_value,table_array,col_index_num,range_lookup))*

The value for which we are trying to retrieve the result from the table array(2*lookup_value*:^{nd }argument)**.**It is a mandatory argument.*table_array***:**This will be either a range or table array where we search for the lookup value. It is a mandatory argument.*col_index_num***:**From the giventhe columns we are looking for the result. Since we are retrieving the values from multiple columns, we need to mention all the required column numbers in a curly bracket ({}).*table_array,***[**In this argument, we need to specify the kind of match we need to do:*range_lookup*]:**0 or FALSE –**It will search for the exact match of thein the*lookup_value*If nothing is specified, 1 or TRUE will be the default mode.*table_array*.

**1 or TRUE –**It will search for the approximate match of thein the*lookup_value*.*table_array*

Let us take a basic example to understand its usage.

We will find out the bonus for Feb of the employee “Sumit Bala” **using VLOOKUP with SUM.**

For instance, we have the following employee monthly bonus data in Excel.

The steps to find the required data **using VLOOKUP with SUM** are,

**Select cell G2, and enter the formula =VLOOKUP(****Choose the lookup_value as cell F2.****Choose the table array as A2:D7 and make it an absolute reference by pressing the F4 key.****Next, enter the column number from which we need the result. Since we need the value from Feb month, enter the column number as 3.****Finally, mention the range lookup as an exact match by entering either FALSE or 0, then close the brackets, and press the “Enter” key. The complete formula so far is =VLOOKUP(F2,$A$2:$D$7,3,0)**

The output, i.e., the bonus value of the employee “**Sumit Bala**” for the month of “**Feb**” is**$7068,**as shown above.

Now, instead of a single month, we need all the months total for the same employee, i.e.,**Jan, Feb,**and**Mar.**For this, when entering the column number need to enter all the month’s column numbers in curly brackets ({}) as shown in the following image.

[**Note:**Column numbers {2,3,4} represent the column numbers for Jan, Feb, and Mar months.

However, if we hit the enter key, we get the**#REF error**value.]**To get the correct result, we will now use the VLOOKUP with SUM formula.**Select cell

**G2**, and enter the formula**=SUM(VLOOKUP(F2,$A$2:$D$7,{3,4,5},0))****Press the “Enter” key, and we will get the #REF error, as shown below.**

Since we have entered multiple column values, we must execute the formula as an array formula. So, press the keys “**Ctrl+Shift+Enter**”. Now the formula is**=SUM(VLOOKUP(F2,$A$2:$D$7,{2,3,4},0))**

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

### Examples

We will understand some advanced scenarios using the **VLOOKUP with SUM examples.**

#### Example #1 – SUM of Matching Values in Columns

Now we will see how to get the sum of all the matching values from the column.

We have the following yearly units sold data of different products in Excel.

We have 6 years of units sold of products from A6:G16. Now we will find the total units sold for the products A, R, and C.

The steps to calculate the **VLOOKUP with SUM **are,

**Step 1:**Select cell**B2**, and enter the formula**=VLOOKUP(**

**Step 2:**Choose the lookup value as cell A2.

**Step 3:**Choose the table array from**A7:G16**and make it an absolute reference in excel by pressing the**F4**key, so that when we copy the same table array reference to the below cells, it should be static.

**Step 4:**Next, we need to enter from which columns we need the data. Since we are looking for all the year’s data, enter the column numbers as {2,3,4,5,6,7}, in curly brackets ({}).

**Step 5:**At the last of the VLOOKUP, give the matching type as FALSE or 0 to get the exact match.

**Step 6:**Now wrap the**VLOOKUP with SUM**function. The complete formula is**=SUM(VLOOKUP(A2,$A$7:$G$16,{2,3,4,5,6,7},0))**

**Step 7:**If we are using the Office 365 version of Excel, then we can simply hit the “**Enter**” key to get the result. And, if we use any other versions, execute the formula with the**Ctrl+Shift+Enter**key.

The total number of units sold across years for product A is **2,40,434**. Now, drag the formula from cells **B2 **to** B4** using the excel fill handle.

We got the result as **#N/A** for product R in cell **B3** because product R is not found in the sales table range **A6:G16.**

To tackle these errors, wrap the entire **SUM **and** VLOOKUP formula** with the **IFERROR excel function** as,

Whenever there is an error, the **IFERROR** will convert the error cell into a blank value, as shown above.

#### Example #2 – SUM of Matching Values in Rows

When the data contains multiple records of the same item, then we need to come up with a different strategy.

For instance, we have the following data in Excel. In the below data, we have multiple entries for products A & C. To get the sum from the multiple columns, we cannot use the **VLOOKUP function; **instead, we will use the **SUMPRODUCT excel function**.

The steps to calculate using the **SUMPRODUCT function** are,

**Step 1:**Select cell**B2**, and enter the formula**=SUMPRODUCT(**

**Step 2:**For the array1 argument, choose the product name list from**D2:D10**.

**Step 3:**Enter an equal sign and give the excel cell reference of the A2 cell.

**Step 4:**Surround this logic with brackets. Doing so will check all the rows in the range**D2:D10**that**are**equal to the value in cell A2.

**Step 5:**Enter the multiple sign asterisk (*) and select the range from table**E2:J10**.

**Step 6:**Close the brackets, and press the “**Enter**” key to get the result. The complete formula is**=SUMPRODUCT(($D$2:$D$10=A2)*$E$2:$J$10)**

Here, the SUMPRODUCT function looks for all the matching records of product A and summates all the matching rows. So, the total units sold for product A is **11,500,** and C is **7,744**.

#### Example #3 – VLOOKUP with SUM

We have the following employee table in Excel. We have 3 tables, A, B, and C.

In table A, we have a unique Product Name and ID, and in table B, we have product id-wise units sold data.

In table C, we need to get the sum of total units sold based on the product name.

Here, we do not have a product name in table B to find the sum of units sold from table B based on the product name. Therefore, we will combine **SUMIF and VLOOKUP** to arrive at the result here.

- To calculate the
**VLOOKUP with SUM,**first, select cell**H3**, and enter the formula**=SUMIF(**

- Choose the range as the product id list from table B,
**D3:D10**.

Now we must have the criteria to consider the product id, but we have the criteria as product name, and not product id. So, based on the product name, we will fetch the product id from table A by using the **VLOOKUP function.**

- Using the
**VLOOKUP function**, we retrieve the product id for product A from table A. Next, enter the sum range as the unit’s column from table B.

- Close the bracket and press the “
**Enter**” key to get the sum of all the rows of product A. The complete formula is**=SUMIF($D$3:$D$10,”=”&VLOOKUP(G3,$A$3:$B$6,2,0),E3:E10)**

The total number of units for product A is **11,992**.

### Important Things To Note

- When we supply multiple column numbers in the
**VLOOKUP function**, we need to enter the column numbers inside the curly brackets ({}), each column number separated by a comma. - If we use the Office 365 version of Excel, we can press the “
**Enter**” key to execute the formula as an array formula. However, if we are using Excel 2016 and earlier versions, then we need to execute the formula as an array formula using the**Ctrl + Shift + Enter**key. - The array formula will have the curly brackets on either side of the formula that is not visible in edit mode.
**VLOOKUP**can do the addition from only one row. If multiple rows exist for the same item, then we need to use the**SUMPRODUCT****function**.

### Frequently Asked Questions (FAQs)

**1. Can I Use SUM and VLOOKUP Together?**

Yes, we can use the **SUM and VLOOKUP functions** together to get the value from multiple columns.

**2. How to SUM and match VLOOKUP in a row?**

Let us consider the following data in Excel to **SUM **and match** VLOOKUP **in a row.

We have multiple records for product A, select cell **F2**, and enter the formula **=SUM(VLOOKUP(E2,$A$2:$C$7,{2,3},0))****VLOOKUP** retrieved the value from Q1 & Q2 for the first record of product A, i.e., row number 2.

However, it did not consider the second entry in the 7^{th} row for product A.

When we need to sum the values from multiple row values, we need to use the **SUMPRODUCT** function, as shown in the following image.

SUMPRODUCT will find the records in the range **A2:A7** for product A and then add the matching row values to get the values from multiple rows.

**3. How to SUM and match a VLOOKUP column in Excel?**

Let us look at the following data in Excel.

For product C, we have three-quarters data. So, using the **VLOOKUP with SUM,** we will add multiple columns of data.

Select cell **G2**, and enter the formula **=SUM(VLOOKUP(F2,$A$2:$D$6,{2,3,4},0))****VLOOKUP** returns the values from the columns {2,3,4} and returns an array of values. Then **SUM function** will add those and returns the overall total as 16,267.

In cell **E4,** we have applied a simple **SUM function** to cross-check the result, matching with the **VLOOKUP **and** SUM function** results.

### Download Template

This article must help understand the **VLOOKUP With SUM** with formulas and examples. You can download the template here to use it instantly.

### Recommended Articles

A guide to VLOOKUP With SUM. Here we learn how to use SUM formula with VLOOK to add numeric values along with examples & downloadable excel template. You can learn more from the following articles –

## Leave a Reply