In this tutorial, we will introduce how to calculate total and get summary data from multiple sheets with similar structure.
1.Calculate Total Based on Cell Reference
This article shows you how to get data from multiple sheets into a master sheet. Depends on the situation, this can be done by multiple methods which will be covered here. Assuming we have 3 sales sheets of 3 different stores: 1, 2 and 3 with similar format below:
We want to add the Total sales of Store 1, 2 and 3 together and put the result in a “Summary” sheet. In this example, although we can use cell reference as in the following formula:
The problem with using the addition operator is that we can’t keep adding cells with more sheets.
2.Calculate Total Based on 3D Formula
Excel has 3D formula to help us sum all data across multiple adjacent sheets. On Summary tab, type =sum( in cell C2, select cell D9 on Store1 tab, hold the Shift key and select the rest of the tab. The formula bar shows:
This 3D formula calculates the total of all D9 cells in different sheets from Store 1 to Store 3.
This formula gives the results the same as using cell reference. See below:
Note that when using this formula, user must make sure to maintain the same order of the sheets. If any of the sheet in the middle of the sheet range Store1to Store3 is moved out of the range, that sheet will be excluded from the calculation. In the screenshot below, we see that when Store2 sheet is moved to the right of Store3 sheet, the Total sale value drops to 645.9 which is Store1 + Store2 sales only.
3.Gather data from multiple sheets using INDIRECT()
We can add data from multiple sheets into a summary sheet using INDIRECT() function. INDIRECT() function need us to store the name of the sheet explicitly.
In the formula above, INDIRECT(C1&”!”&”D9″) gives us the total sales located at cell D9 on Store1 sheet, INDIRECT(D1&”!”&”D9″) gives us the total sales located at cell D9 on Store2, and so on.
This formula is better than 3D formula because it does not depend on the sheet location. However, the formula requires a list of all the sheet names.
As you have seen from the tutorial above, gathering data from multiple sheets can be done in several different ways and there is no best solution. Depends on the situation, one can use 3D formula or INDIRECT() function. In some other cases, you can also use Data Consolidation to calculate the total. Different problems have their own best solution. If you are stuck and want to get to the solution quick, ask your question to have it answered by an Excel expert in 20 minutes. They are available to help you 24/7 at the link to the right. The first question is free.