Sum Excel Worksheets Technique
Why haven’t I known about this simple Excel technique before now?
Maybe you were aware how you can easily Sum Excel Worksheets but I never seen this in all my years of Excelling.
This trick works if you have your data setup on multiple worksheets in an Excel workbook in the Exact same cells on each tab. Some users like to do this so that they can create reports by month or by grouping. For instance, if you want your workbook to be for 2017 and each tab is a different month, then you would definitely want to know this Excel trick.
I imagine that I haven’t seen this tip before because I don’t set up my data in this manner. I am not sure I would every put my data in this format but I think it is a common format. Instead, I would put my data in one tab vs a tab for each month. Then I would either create a pivot table for the month I wanted to see or create a report that looks up data that I would want for that month. But enough about that. What is the trick?
The trick is that you can use the standard Excel SUM Function, but you just have to change it a little bit.
If you have your worksheet tabs set up as you see there (12 worksheets from December to January):
You can use this formula to get the Sum of all values in cell A2 for each of the worksheets:
It is a simple Sum function but we modify the reference to from just one sheet to multiple worksheet tabs by adding the “:” colon and then the cell reference.
The Great news is that Excel uses the first worksheet name value as the left most worksheet tab and the second worksheet name as the right most worksheet tab and then it sums all of the tabs between. So any of the worksheet tabs in between those 2 named in the formula will be summed regardless of the worksheet tab names or ids. As long as they are between the 2 worksheets named it will get summed. You can rearrange the tabs and it won’t affect your sum, however, if you move a tab into or out of the Dec or Jan then your value will adjust for any values in cell A2 on those worksheets.
I recommend you watch the video below to see what I mean it is really quick and demonstrates what I mean. Now you can do this with most Excel Functions, but I am not sure you can do it with all of them. Check out the Friday Challenge below and see if you can find a solution.
For the spreadsheet link below, see if you can find the Average excluding Zeros from your average across All Dec-Jan worksheet tabs for cell A2 on all worksheets.
Post the formula you used in the comments below as well as the version of Excel you are using. This may not be as easy as you think 🙂 but maybe I made it too complicated. I am excited to see your answers in the comments below!
Challenge Download File: 2017-Sales-Sum-Across-Tabs.xlsx