A number of months ago I received a spreadsheet from a work colleague. As I reviewed the information I noticed a rather interesting formula. Based on the syntax of this formula I was convinced there must be an error. But after 15 minutes of checking, I concluded that my colleague was an Excel genius – he had used the SUM formula in a way which had never occurred to me. “How did you not know that? Everybody knows that!” he said, with stunned amazement.
Since I didn’t know this little trick, I decided it would be a great trick to share. The best thing is that it’s exceptionally simple.
SUM the same cell across multiple worksheets
It is possible to sum the same cell across multiple of worksheets using this formula
Replace FirstSheet and LastSheet with the names of the worksheets you wish to sum between. In this example, A1 would be the cell reference to sum.
In the past, I would have clicked on every page putting a “+” symbol between each reference. If there were a lot of worksheets it could take a while to click on them all. Also, if the sheet names are long the formula starts to look quite unreadable.
Even with just 12 sheets it can become unreadable:
How do you know if you’ve clicked on every worksheet? What if you happened to miss one by accident? There is only one way to know – you’ve got to check it!
However, with this beautiful little formula you can actually see all the worksheets which are between the first and the last sheet. So you can see what is included or excluded.
When using this system, if a new worksheet is added outside the range of the named worksheets it won’t be included in the calculation. So, to make it even easier, I add a blank sheet at the start called “Start” and one at the end called “End”.
These sheets have no values on them, so even though they are included in the calculation, they do not change the value.
This system is great to quickly summarize or update for different periods, just drag worksheets in and out of the sheets between the Start sheet and End Sheet.
Which other formulas does this work for?
This doesn’t just work for the SUM function. Here is a list of all the functions for which this trick works.
Min & Max
There are just two things to be aware of.
- All of the sheets must be a consistent layout and must stay in a consistent format. If one worksheet changes then the formula won’t SUM the correct cells.
- Normally, cell references move automatically when new rows or columns are inserted. This formula does not work like that. It will only move if you select all the sheets and then insert a row or column into all of those sheets at the same time.