I’ve set up 4 sheets:
Each of the years has data for days of the week and months of that year:
I also have a sheet called Total in which I want to add up the 3 years of information by cell. Note that the tables need to be structured identically. To help in doing this, you may want to review my blog about grouping here: http://goo.gl/v1DMB.
We know that we can manually go through each sheet and add the specific cells to get the sum of that data, resulting in a formula such as =’2010′!B2+’2011′!B2+’2012′!B2.
But if you have 10 or 15 or 30 sheets, that could take some time and open yourself to errors. An easier way is to use 3D Sum. To get the total of the Mondays in January for all 3 years, in cell B2, enter the following formula:
The syntax is =SUM(first_sheet_name:last_sheet_name!cell_reference). Enter the first sheet name and last sheet name separated by a colon, then and exclamation point and the cell reference. The result is 129, which is the sum of the values in B2 of the three sheets: 46+34+49 = 129.
Now just copy that formula to the rest of your table and you’ve filled in your data:
Another great Excel time saver!