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.
VLOOKUP and SUM Across Multiple Sheets in Excel: 2 Suitable Ways
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 Function with Exact Match in Excel
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: VLOOKUP with IF Condition in Excel
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.
Download Practice Workbook
Before we start, I would like to request you download the following workbook for your self-practice.
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.
Related Readings
- Combining SUMPRODUCT and VLOOKUP Functions in Excel
- How to Use VLOOKUP with COUNTIF
- How to Combine SUMIF and VLOOKUP in Excel
- Use VLOOKUP to Sum Multiple Rows in Excel
- INDEX MATCH vs VLOOKUP Function
- Excel LOOKUP vs VLOOKUP
- XLOOKUP vs VLOOKUP in Excel
- How to Use Nested VLOOKUP in Excel
- IF and VLOOKUP Nested Function in Excel
- How to Use IF ISNA Function with VLOOKUP in Excel
- How to Use VLOOKUP Function with INDIRECT Function in Excel
- How to Use IFERROR with VLOOKUP in Excel
