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:

More Easy Excel Tricks

The easiest way to reduce file size in Excel: Excel Binary Workbook
An easier way to calculate compound interest in Excel
Unhide all sheets in Excel with a simple macro that works for all your documents
Make SUMIFS and COUNTIFS in Excel more flexible with a Wildcard

Are you using a non-English version of Excel? Click here for translations of the 140 most common functions in 17 different languages:

Catalan
Czech
Danish
Dutch
Finnish
French
Galician
German
Hungarian
Italian
Norwegian
Polish
Portuguese (Brazilian)
Portuguese (European)
Russian
Spanish
Swedish
Turkish

Leave a Reply

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


one × = 6