How to Vlookup and Sum Across Multiple Sheets in Excel (2 Formulas)

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.

Sample Dataset: Vlookup and Sum across Multiple Sheets

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:

=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))

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. 👇

Using the VLOOKUP and SUM Functions to Lookup and Sum Data Across Multiple Sheets: Result

📌 Formula Description:

  • Formula:

=SUM(number1,[number2],[number3],..)

Output: It takes the numbers and gives the sum of the numbers.

  • Formula:

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

Arguments:

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.

Read More: How to Use VLOOKUP with SUM Function in Excel (6 Methods)


Similar Readings


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:

Step 1:

  • Write the sheet names and select them.

Month list: Vlookup and Sum Across Multiple Sheets Using the SUMPRODUCT, SUMIF and INDIRECT Functions

Step 2:

  • Click on the Name Manager from the Formulas tab.

Finding Name Manager from the Formulas tab

Step 3:

  • This will open a dialog box named Name Manager. Click on New at the left top of the new box.

Selecting New from Name Manager box

Another small box will appear.

Step 4:

  •  Write:-
» The name of the list, “Months” in the Name section.

» Let the Scope section be Workbook (by default).

  • Check the range in Refers to:
  • If everything looks like the below picture click OK.

Writing Name and range of data to form list

Step 5:

  • Clicking OK will take you back to the first box where you can see that there is the new list.

Checking the new list

Step 6:

  • After that, click Close.
  • Write the formula below to get results for sales of 3 months for Product ID #1101.
=SUMPRODUCT(SUMIF(INDIRECT("'"&Months&"'!C5:C9"),$B$5,INDIRECT("'"&Months&"'!D5:D9")))
  • Applying the formula you will get the result for Product ID #1101.

Vlookup and Sum Across Multiple Sheets Using the SUMPRODUCT, SUMIF and INDIRECT Functions: Result

Step 7:

  • Drag the fill handle icon to get the result for the rest of the Product ID.

📌 Formula Description:

  • Formula:

=SUMPRODUCT(array1,[array2],[array3]..)

Output: It takes arrays and gives the sum of the products of the corresponding ranges and arrays.

  • Formula:

=SUMIF(range, criteria, [sum_range])

Arguments:

range= the range where summation will take place.

criteria=the condition on which the sum will take place.

sum_range=range for summation.

  • Formula:

=INDIRECT(ref_text,[a1])

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.

Read More: Sum If a Cell Contains Text in Excel (6 Suitable Formulas)


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.


Conclusion

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.


Related Readings

Syeda Fahima Nazreen

Syeda Fahima Nazreen

Hello People! This is Syeda Fahima Nazreen. I have completed my Bachelors in Science in Electrical and Electronic Engineering. I love to do research and work anything related to technology which includes research and development. I feel great to share my knowledge with you people and your thoughts and opinions about my writing is highly appreciated by me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo