# How to Combine SUMIF and VLOOKUP in Excel (3 Approaches)

Get FREE Advanced Excel Exercises with Solutions!

In Microsoft Excel, we have to combine SUMIF and VLOOKUP functions to look for a value, and based on that criterion, a sum is calculated from a range of values. In this article, youâ€™ll get to learn a few useful techniques to combine SUMIF and VLOOKUP functions with some suitable examples and simple explanations.

## Overview: Excel SUMIF Function

• Objective:

The function adds the cells specified by a given condition or criteria.

• Formula:

=SUMIF(range, criteria, [sum_range])

• Arguments:

range- The range of cells where the condition will be applied. criteria- Condition for the selected range of cells. [sum_range]- The range of cells where the outputs are lying. For more detailed explanations and examples with the SUMIF function, click here.

## Overview: Excel VLOOKUP Function

• Objective:

The VLOOKUP function looks for a value in the leftmost column in a table and then returns a value in the same row from a specified column.

• Formula:

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

• Arguments:

lookup_value- The value which it looks for in the leftmost column of the given table. Can be a single value or an array of values. table_array- The table in which it looks for the lookup_value in the leftmost column. col_index_num- The number of the column in the table from which a value is to be returned. [range_lookup]- Tells whether an exact or partial match of the lookup_value is required. 0 for an exact match, 1 for a partial match. Default is 1 (partial match).

When we have to combine SUMIF and VLOOKUP functions, the scenario can be considered into two categories. i) We have to look for the matches and then make a sum based on the findings. Or, ii) We have to make a sum from multiple tables or worksheets and then look for the match with the VLOOKUP The first two methods in the following cover the initial criteria while the 3rd method will sum up the second one.

## 1. Combining SUMIF with VLOOKUP to Find Matches and Sum in the Same Excel Worksheet

Letâ€™s get introduced to our dataset first. The first table (B4:D14) represents some random order data with product IDs and their corresponding prices. The second table on the right shows the customer names and their IDs. What weâ€™ll do here is search for a specific customer name present in Cell C16 and then based on this only information, weâ€™ll look for the orders for the corresponding customer and make a sum of the total price to be paid in Cell C17.

In the output Cell C17, the required formula with the SUMIF and VLOOKUP functions will be:

`=SUMIF(B5:B14,VLOOKUP(C16,F5:G11,2,FALSE),D5:D14)`

After pressing Enter, youâ€™ll get the return value as shown in the picture below.

ðŸ”Ž How Does the Formula Work?

• In this formula, the VLOOKUP function works as the second argument (Criteria) of the SUMIF function.
• The VLOOKUP function looks for the name Alex in the lookup array (F5:G11) and returns the ID number for Alex.
• Based on the ID number found in the previous step, the SUMIF function adds up all the prices for the corresponding ID number.

## 2. Joining SUMIF with VLOOKUP to Find Matches and Sum in Different Worksheets

In this section, weâ€™ll apply the same procedures described before but this time, the lookup array or table is present in another worksheet (Sheet2). So, when we need to refer to the lookup array where the customer names and their IDs are situated, weâ€™ll have to mention the related Sheet name too. The following worksheet (Sheet1) contains the primary data with the output cell.

And hereâ€™s the second worksheet (Sheet2) where the lookup array is present.

To include the above lookup array in the VLOOKUP function, we have to mention the worksheet name (Sheet2). This worksheet name will be automatically inserted when youâ€™ll switch to Sheet2 and select the lookup array for the VLOOKUP function. So, the final formula in the output Cell C17 will be:

`=SUMIF(B5:B14,VLOOKUP(C16,Sheet2!B3:C9,2,FALSE),Sheet1!D5:D14)`

Now press Enter and youâ€™ll get the resultant value as shown below.

Read More: VLOOKUP and Return All Matches in Excel

## 3. Combining VLOOKUP, SUMPRODUCT, and SUMIF Functions for Multiple Excel Sheets

Now weâ€™ll work with multiple spreadsheets. In this method, weâ€™ll make a sum from the data available in different two different worksheets and then extract a value with the VLOOKUP function based on the corresponding amount of the sum. In the picture below, 1st worksheet named Bonus_Amount is present with 3 different tables. The leftmost table will show the sales bonuses for the corresponding sales representatives. We have to extract these bonus amounts by applying the VLOOKUP function for the array (E5:F8) related to the bonus criteria. The bonus criteria are actually the total sales which we have to pull out from two different worksheets named â€˜Day 1â€™ and â€˜Day 2â€™.

The following worksheet is the sales data for Day 1 in November 2021.

And another worksheet with the name â€˜Day 2â€™ is here with the sales data for the second day.

In the 1st worksheet (Bonus_Amount), the required formula in the output Cell C5 will be:

`=VLOOKUP(SUMPRODUCT(SUMIF(INDIRECT("'"&\$H\$5:\$H\$6&"'!"&"B5:B10"),Bonus_Amount!B5,INDIRECT("'"&\$H\$5:\$H\$6&"'!"&"C5:C10"))),\$E\$5:\$F\$8,2,TRUE)`

After pressing Enter and using the Fill Handle to autofill the rest of the cells in the Bonus column, weâ€™ll obtain the following outputs.

ðŸ”Ž How Does the Formula Work?

• In this formula, the INDIRECT function refers to the Sheet names from Cells H5 and H6.
• The SUMIF function uses the reference sheets (Obtained by the INDIRECT function) to include the sum range and criteria for its arguments. The resultant outputs from this function return an array that represents the sales amounts for a specific salesperson from Day 1 and Day 2.
• The SUMPRODUCT function adds up the sales amounts found in the previous step.
• The VLOOKUP function looks for the range of this total sales amount in the table (E4:F8) of Bonus Criteria in the Bonus_Amount sheet. And finally, it returns the bonus amount based on the criteria range for a salesperson.

## How to Use SUMIFS with VLOOKUP to Add Multiple Criteria in Excel

The SUMIFS function is able to intake multiple criteria or conditions. By combining this function with VLOOKUP, we can look up a value, add some criteria and finally get a sum considering the mentioned criteria for the lookup value. The dataset in the following is quite similar to the one we have seen in the first two methods. In this table, weâ€™ve added a new column after the Price column. The new column represents the order statuses for all order IDs. By using the SUMIFS function here, weâ€™ll insert two criteria- i) The specific order ID for a customer, and ii) Order Status as â€˜Confirmedâ€™ only.

The required formula in the output Cell C17 will be:

`=SUMIFS(D5:D14,B5:B14,VLOOKUP(C16,G5:H11,2,FALSE),E5:E14,"Confirmed")`

Now press Enter and youâ€™ll get the total price of the conformed orders for Marcus.

Read more: How to Use IF ISNA Function with VLOOKUP in Excel

## Concluding Words

I hope all of these simple methods mentioned above will now help you to apply them in your Excel spreadsheets when you have to combine SUMIF with the VLOOKUP function. If you have any questions or feedback, please let me know in the comment section.

## Related Articles

<< Go Back to Excel VLOOKUP Sum | Excel VLOOKUP Function | Excel Functions | Learn Excel

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags: