Syeda Fahima Nazreen
In general, corporate databases are usually complex and contain multiple worksheets in workbooks. However, 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. In this article, I will show you 2 suitable ways to vlookup and sum across multiple sheets in Excel with the VLOOKUP, SUM, SUMIF, SUMPRODUCT, and INDIRECT functions. Hence, read through the article to learn more and save time.
Download Practice Workbook
Before we start, I would like to request you download the following workbook for your self-practice.
2 Suitable Ways to VLOOKUP and SUM Across Multiple Sheets in Excel
For the purpose of demonstration, I have used the following sample dataset. Here, we have 3 sheets in an Excel workbook which we will use to vlookup and get the sum across these multiple sheets. Hence, the 3 sheets are shown below.
In addition, you can see there are three sheets for 3 consecutive months: January, February, and March. However, 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. Use VLOOKUP and SUM Functions Across Multiple Sheets
In the beginning, we will use VLOOKUP and SUM functions across multiple sheets to complete the task. Fortunately, the process is quite simple and easy. Hence, read through the steps mentioned below in order to complete the operation properly in Excel.
📌 Steps:
- Initially, select cell C5 and write down the following formula.
=SUM(VLOOKUP(B5,January!$B$5:$D$9,{3},FALSE),VLOOKUP(B5,February!$B$5:$D$9,{3},FALSE),VLOOKUP(B5,March!$B$5:$D$9,{3},FALSE))
📌 Formula Description:
- Firstly, in the VLOOKUP function, we are looking up for Product Name in cell B5.
- Secondly, we have added all the 3 sheets’ table range.
- Thirdly, 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.
- Fourthly, we wanted an exact match. Thus, used FALSE. However, you can use TRUE also in case you want an approximate match.
- Lastly, the SUM function provides the sum of the matched items.
- Afterward, hit the Enter button and utilize the AutoFill tool to the entire column.
Read More: How to Use VLOOKUP with SUM Function in Excel (6 Methods)
Similar Readings
- 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 Applying SUMPRODUCT, SUMIF, and INDIRECT Functions
Furthermore, 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. However, the process is similar to before and simple to use. Now, let’s follow the steps below.
📌 Steps:
- Firstly, write the sheet names and select them.
- Secondly, click on the Define Name from the Formulas tab.
- Thirdly, write the name of the list, “Months” in the Name section.
- Fourthly, check the range in Refers to: and click OK.
- After that, write the formula below in cell C5 to get results for sales of 3 months for Product ID #1101.
=SUMPRODUCT(SUMIF(INDIRECT("'"&Months&"'!C5:C9"),$B$5,INDIRECT("'"&Months&"'!D5:D9")))
📌 Formula Description:
- Initially, the INDIRECT function 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.
- Then, the SUMIF function checks the range and criteria to find the desired sum.
- Thereafter, the SUMPRODUCT function takes arrays and gives the sum of the products of the corresponding ranges and arrays received already.
- Finally, press Enter and apply the AutoFill tool to the whole column in order to get your desired output.
Read More: Sum If a Cell Contains Text in Excel (6 Suitable Formulas)
Things to Remember
- First of all, you must follow the syntax of the formulas if you have a different dataset.
- Then, to write arguments from different sheets you can simply click on the sheet and select the required data.
- Lastly, formulas will extend with the number of sheets you have in your dataset.
Conclusion
These are all the steps you can follow to vlookup and sum across multiple sheets in Excel. Overall, in terms of working with time, we need this for various purposes. I have shown multiple methods with their respective examples, but there can be many other iterations depending on numerous situations. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.
For more information like this, visit Exceldemy.com.
Related Readings
- 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)
