From the Not Just Numbers blog:
Have you ever needed to sum the same cell across multiple sheets?
You might have 12 monthly sheets and an annual summary or 26 department sheets and a consolidation.
I don’t normally design spreadsheets that require this as it tends to go against my advice on how to structure your data.
I would generally advise one data input sheet that covers all months (or all departments) and a report that will present the information for any particular month (or department).
However, I see many spreadsheets that are structured like this and there are times when it is appropriate. In this post, I will show you a quick way to do it.
A reader asked me about this last week as she thought I had written about it in the past, and I had to look it up (as I said, I don’t normally have a need for it).
Before I looked it up, I would enter a formula such as:
=Worksheet1!A1+Worksheet2!A1+Worksheet3!A1+Worksheet4!A1+Worksheet5!A1
…to add cell A1 from 5 different worksheets.
This could get very tiresome, of you had, say, 100 worksheets!
The much more efficient way to do it is:
=SUM(‘Worksheet1:Worksheet5’!A1)
Note that it is the position of the worksheets that matters, not their name. The formula will sum all worksheets between Worksheet1 and Worksheet5 inclusive – left to right.
The reader, when I showed her this, remembered this useful tip from wherever she had read it. Use ‘bookend’ worksheets if the worksheets to add may change, e.g. have worksheets named First and Last, and place any sheets to be included in the sum in between them.
My thanks to the post that helped me to answer the question:
http://www.ozgrid.com/forum/showthread.php?t=73877
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.