How to Combine Excel SUMIF & VLOOKUP Across Multiple Sheets

Combining Excel SUMIF & VLOOKUP functions is one of the most popular formulas to gather values from multiple sheets and sum values based on a criterion. In this article, we are going to learn about how to do that with multiple examples and explanations.


Practice Workbook

Download the following workbook and exercise.


Introduction to the Excel SUMIF Function

SUMIF function sums up the values based on a particular condition.

  • Syntax:

=SUMIF(range, criteria, [sum_range])

  • Arguments:

range: The range of values to sum

criteria: Condition to use in the selected range

[sum_range]: Where we want to see the result.

Introduction to the Excel VLOOKUP Function

VLOOKUP function looks for a value in a vertically organized table and returns the matched value.

  • Syntax:

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

  • Arguments:

lookup_value: What we want to lookup.

table_array: From where we want to lookup.

column_index: The number of columns in the range containing the return value.

[range_lookup]: For exact match = FALSE, Approximate / Partial match = TRUE.


2 Easy Ways to Combine Excel SUMIF & VLOOKUP Across Multiple Sheets

1. Use of Excel SUMIF Function with VLOOKUP Function Across Multiple Sheets

SUMIF function works like SUM function but it sums up only those values that match the given condition. We are going to use the VLOOKUP function inside the SUMIF function to input the criteria. Assuming we have two worksheets (Sheet1 & Sheet2). In Sheet1 we have all the employee’s ID No and their sales amount with the price in range B4:D9.

Use of Excel SUMIF Function with VLOOKUP Function Across Multiple Sheets

In Sheet2, we have all the employees’ names with their ID No.

Use of Excel SUMIF Function with VLOOKUP Function Across Multiple Sheets

Here we are going to search for the employee Lily (Cell C11) of Sheet1. Now from Sheet2, we are going to look for her ID No and show the total sum up sales prices in Cell C12 (Sheet1).

STEPS:

  • First, select Cell C12 in Sheet1.
  • Now type the formula:
=SUMIF(B5:B9,VLOOKUP(C11,Sheet2!B5:C9,2,FALSE),Sheet1!D5:D9)

  • Then hit Ctrl+Shift+Enter to see the result.

Formula Breakdown

VLOOKUP(C11,Sheet2!B5:C9,2,FALSE)

This will look up the ID No for the value of Cell 11 of Sheet1 from Sheet2 cell range B5:C9. Then returns the exact match.

SUMIF(B5:B9,VLOOKUP(C11,Sheet2!B5:C9,2,FALSE),Sheet1!D5:D9)

This will sum up all the prices, based on the exact match of ID No from the previous step.

Notes:

  • If you’re not an Excel 365 user, then to get the final result, you have to press Ctrl+Shift+Enter as VLOOKUP works as an array formula.
  • The column index number cannot be less than 1.
  • SUMIF function only works on numerical data.

2. Combine SUMIF, VLOOKUP & INDIRECT Functions Across Multiple Sheets

In this section, we are going to use SUMPRODUCT & INDIRECT functions with VLOOKUP & SUMIF functions for multiple worksheets. Here we have three worksheets. In the first worksheet ‘Bonus’, we can see the employee names. We have to find out the amount of bonus for each employee. There is also a bonus criteria table (E4:F7) showing the bonus amount based on the sales amount. We need to extract values from Month 1 & Month 2 worksheets.

Combine SUMIF, VLOOKUP & INDIRECT Functions Across Multiple Sheets

Now the sales of Month 1 are on the below worksheet.

Combine SUMIF, VLOOKUP & INDIRECT Functions Across Multiple Sheets

And the sales of Month 2 are on the below worksheet.

Combine SUMIF, VLOOKUP & INDIRECT Functions Across Multiple Sheets

STEPS:

  • First, select Cell C5 of the Bonus worksheet.
  • Next type the following formula:
=VLOOKUP(SUMPRODUCT(SUMIF(INDIRECT("'"&$H$5:$H$6&"'!"&"B5:B9"),Bonus!B5,INDIRECT("'"&$H$5:$H$6&"'!"&"C5:C9"))),$E$5:$F$7,2,TRUE)

  • Finally, hit Enter and use Fill Handle to see the rest of the result.

Formula Breakdown

➤ The INDIRECT function converts the text string into a valid cell reference. Here it will refer to the sheets from the cell range H5:H6.

➤ To include the range of sum and criteria, the SUMIF function will use reference worksheets that we indicated. It will return the value sales amount of each employee from the worksheets Month 1 & Month 2.

➤ The SUMPRODUCT function will sum up the amounts we found from the above procedure.

➤  In the Bonus worksheet, the VLOOKUP function looks up from the range E5:E7. In the end, it will return the matched bonus amount of an employee.

Notes:

  • The column index number won’t be less than 1.
  • Input the index number as a numerical value.
  • SUMIF function only works on numerical data.
  • We should press Ctrl+Shift+Enter as VLOOKUP works as an array formula.

Conclusion

By using these methods, we can easily combine Excel SUMIF & VLOOKUP functions across multiple sheets to find a value. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.


Related Readings

Excel Dynamic VLOOKUP (with 3 Formulas)

How to Remove Blank Cells in Excel (10 Easy Ways)

Extract Text Before Character in Excel (4 Quick Ways)

How to Pull Data From Another Sheet Based on Criteria in Excel

Count Specific Characters in Excel Cell (4 Quick Ways)

Nuraida Kashmin

Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. I am a Team Leader of Excel and VBA Content Developer in ExcelDemy. Here I will also post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo