This post may contain affiliate links. Please read my disclosure for more info.

How to sum the same cell across multiple worksheets

Excel-Sum-Across-Worksheets-Image

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

=SUM(‘FirstSheet:LastSheet’!A1)

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:

sumacrosssheets1

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.

sumacrosssheets2

My system

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”.

sumacrosssheets4

These sheets have no values on them, so even though they are included in the calculation, they do not change the value.

sumacrosssheets3

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.

Basic Calculations

  • SUM
  • AVERAGE
  • AVERAGEA
  • PRODUCT

Counting

  • COUNT
  • COUNTA

Min & Max

  • MAX
  • MAXA
  • MIN
  • MINA

Standard deviation

  • STDEV
  • STDEVA
  • STDEVP
  • STDEVPA

Variance

  • VAR
  • VARA
  • VARP
  • VARPA

The drawbacks

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.

Save



Want to Learn VBA & Macros?

If you want to automate Excel and save time, my recommended resouces for learning VBA Macros are:

2 thoughts on “How to sum the same cell across multiple worksheets

  1. Angel says:

    I have multiple workbooks that I add daily tabs to. How do I add a certain cell or area keeping the previous day’s data the same but find or keep total data information?

Leave a Reply

Your email address will not be published. Required fields are marked *