Suppose you have 12 identical table across worksheets, for each month. And you want to take sum of each of the cells across all the worksheets. You would use mouse and keyboard to browse each of the sheets and select the cells to get this =Jan!E6+Feb!E6+Mar!E6+Apr!E6…Dec!E6
Todays post is to show you a shorter way.
A simple way to do that is use a 3-D formula like this one =SUM(Jan:Dec!E6)
Todays post is to show you a shorter way.
A simple way to do that is use a 3-D formula like this one =SUM(Jan:Dec!E6)
Caution: Ensure that the summary sheet is not anywhere between the Jan to Dec sheets, or it will lead to circular values
Now suppose, the sheets are not named after months, but sales data for each quarter or years. You can use similar formula like this =SUM('Qtr1:Qtr4'!E6) or =SUM('Qtr*'!E6) using wildcards.
Another alteration to this would be sheets containing month and year like "Jan 2013" to "Dec 2013", in which case, you need to use a formula like this =SUM(‘Jan 2013:Dec 2013’!E6)
Try it on your own, should you have any questions let me know.
Another huge tip on this would be using named range.
This is how you can set up a named range, referring to the first worksheet.
1. Goto Jan sheet, select cell E6
2. Select Formula > Define Name (I am using Excel 2012)
3. Give a name, the Refers to section will point to =Jan!E6.
4. Click the box, hold down the Shift button and click the Dec sheet. The refers to box will change to =Jan:Dec!E6
