SUM across multiple sheets in Excel

If you have a report in Excel with multiple identical sheets, for example one sheet per month, that you want to add together in a separate sheet, there are a few different ways to do it.

The most common way is to simply type an equal sign, click on B2 in the ‘Jan’ sheet, type a plus sign, click on B2 in the ‘Feb’ sheet, and so on. Then copy down and across. This takes a long time, and it’s easy to make mistakes when you write formulas like that.

A better way to do it is to use this formula:

=SUM(Jan:Dec!B2)
(and copy down and across)

With this formula, Excel looks at all the tabs from Jan through Dec and picks up the value in B2 in all of them. Note that you only need an exclamation mark after the second sheet reference.

You have to be careful when you use this approach, though. If you insert or move another sheet between the ones you want to refer to in the formula, Excel assumes that you want to include that too. On the picture below you can see what might happen if you move a phone list in between the sheets: