Corporate databases are usually complex and contain multiple worksheets in workbooks. You might like to work with ease and be able to extract data across multiple sheets by the Excel VLOOKUP and other functions and sum them up. This article contains a demonstration of 2 formulas to vlookup, then extract data across multiple sheets with the VLOOKUP, SUM, SUMIF, SUMPRODUCT, and INDIRECT functions. It will also show how to sum the extracted data.
Download Practice Workbook
Before we start, I would like to request to download the following workbook for your self-practice.
2 Formulas to VLOOKUP and Sum Across Multiple Sheets in Excel
Assume that we have 3 sheets in an Excel workbook which we will use to Vlookup and get the sum across these multiple sheets.
The 3 sheets are shown below.
You can see there are three sheets for 3 consecutive months: January, February, and March. The sheets contain the Product Name, Product ID, and Sales for a month of January, February, and March (one in each sheet). We will see how to apply the VLOOKUP function for a value and get the result as the sum of the sales for 3 months.
1. Using the VLOOKUP and SUM Functions to Lookup and Sum Data Across Multiple Sheets
Excel has functions with the name as the title. The formula using VLOOKUP and SUM is:
All you need to do is select a cell where you want the sum result and type the formula mentioned above in the cell. Then dragging the Fill handle icon over the rest of the cells in the range will do the rest to get results in that cells.
- See the following picture. 👇
📌 Formula Description:
Output: It takes the numbers and gives the sum of the numbers.
lookup_value= the looked-up value. Here we are looking up for Product Name in cell B5.
table_array=the table from where the searched value will be found. For this case, we have added all the 3 sheets table range,
col_index_num=the column index from where the lookup value is looked up. The column index for the dataset for all the 3 sheets is 3 as we want the result of sales which is in the D column whose index number is 3.
range_lookup=TRUE or FALSE. TRUE for approximate match and FALSE for an exact match. We wanted an exact match, Thus used FALSE. You can use TRUE also in case you want an approximate match.
Note: To write arguments from different sheets you can simply click on the sheet and select the required data.
- Sum All Matches with VLOOKUP in Excel (3 Easy Ways)
- How to VLOOKUP with Multiple Conditions in Excel (2 Methods)
- VLOOKUP Example Between Two Sheets in Excel
- Sum Between Two Numbers Formula in Excel
- How to Sum If Cell Contains Specific Text in Excel (6 Ways)
2. Vlookup and Sum Across Multiple Sheets Using the SUMPRODUCT, SUMIF, and INDIRECT Functions
However, you can also use nested formulas with the functions SUMPRODUCT, SUMIF, and INDIRECT to vlookup and sum across multiple sheets
For this, firstly you need to make a list of the sheet names you have.
Let’s follow the steps below:
- Write the sheet names and select them.
- Click on the Name Manager from the Formulas tab.
- This will open a dialog box named Name Manager. Click on New at the left top of the new box.
Another small box will appear.
» Let the Scope section be Workbook (by default).
- Check the range in Refers to:
- If everything looks like the below picture click OK.
- Clicking OK will take you back to the first box where you can see that there is the new list.
- After that, click Close.
- Write the formula below to get results for sales of 3 months for Product ID #1101.
- Applying the formula you will get the result for Product ID #1101.
- Drag the fill handle icon to get the result for the rest of the Product ID.
📌 Formula Description:
Output: It takes arrays and gives the sum of the products of the corresponding ranges and arrays.
=SUMIF(range, criteria, [sum_range])
range= the range where summation will take place.
criteria=the condition on which the sum will take place.
sum_range=range for summation.
Output: It returns the reference specified by a text string. For this dataset, we have used the “Months” list which we created at the beginning of the process.
Things to Remember
1. You must follow the syntax of the formulas if you have a different dataset.
2. Formulas will extend with the number of sheets you have in your dataset.
The article demonstrated 2 useful formulas to vlookup and sum across multiple sheets. We have used 3 sheets. The 2 formulas include functions like SUM, SUMIF, VLOOKUP, SUMPRODUCT, and INDIRECT. I hope this article was helpful to you. If you have any further query you can ask in the comment section.
- Sum Cells in Excel: Continuous, Random, With Criteria, etc.
- Use of VLOOKUP in VBA to Find Values from Another Worksheet in Excel
- How to Sum Range of Cells in Row Using Excel VBA (6 Easy Methods)
- Highlight Duplicates Across Multiple Worksheets in Excel (3 Formulas)
- How to Sum Multiple Rows and Columns in Excel
- Excel Sum If a Cell Contains Criteria (5 Examples)
- How to Sum Cells with Text and Numbers in Excel (2 Easy Ways)