Sum across multiple sheets in Excel

Have you ever had to sum the same cell across multiple sheets?  This often occurs where information is held in numerous sheets in a consistent format.  For example, it could be a monthly report with a tab for each month (see screenshot below as an example).

I see many examples where the user has clicked the same cell on each sheet, putting a “+” symbol between each reference. If there are a lot of worksheets, it takes a while to click on them all.  Also, if the sheet names are long, the formula starts to look quite unreadable.  The screenshot below shows an example of this type of approach.

The formula in cell C5 is:

=Jan!C5+Feb!C5+Mar!C5+Apr!C5+May!C5+Jun!C5+
Jul!C5+Aug!C5+Sep!C5+Oct!C5+Nov!C5+Dec!C5

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!

The chances are that you don’t need to do all that clicking.  And just think about the time you will waste if there is a new tab to be added.

The good news is that there is another approach we can take that will enable us to sum across different sheets easily.  I still remember the first time a work colleague showed me this trick; my jaw hit the ground in amazement.  I thought he was an Excel genius.  That’s why I’m sharing it here; by using this approach, you can look like an Excel genius to your work colleagues too 🙂

SUM across multiple sheets – basic

To sum the same cell across multiple sheets of a workbook, we can use the following formula structure:

=SUM('FirstSheet:LastSheet'!A1)
  • Replace FirstSheet and LastSheet with the worksheet names you wish to sum between.  If your worksheet names contain spaces, or are the name of a range (e.g., Q1 could be the name of a sheet or a cell reference ), then single quotes ( ‘ ) are required around the sheet names.  If not, the single quotes can be left out.
  • Replace A1 with the cell reference you wish to use

With this beautiful little formula, we can see all the worksheets included in the calculation just by looking at the tabs at the bottom.  Take a look at the screenshot below.  All the tabs from Jan to Dec are included in the calculation.

The formula in cell C5 is:

=SUM(Jan:Dec!C5)

SUM across multiple sheets – dynamic

We can change this to be more dynamic, making it even easier to use.  Instead of using the names of the first and last tabs, we can create two blank sheets to act as bookends for our calculation.

Take a look at the screenshot below.  The Start and End sheets are blank.  By dragging sheets in and out of the Start and End bookends, we can sum almost anything we want.

The formula in cell C5 is

=SUM(Start:End!C5)

We can also create sub-calculations.  For example, we could add quarters as interim bookends too.  There is the added advantage that the tabs also serve as helpful presentation dividers.  The example below shows the calculation of just Jan, Feb, and Mar sheets.

The formula in cell C5 is:

=SUM('Q1:Q2'!C5)

Excel could mistake Q1 and Q2 as cell references; therefore, adding single quotes around the sheet names is essential.

Formula Magic with Dynamic Arrays

Of all the features available in Excel dynamic arrays provide the most power for the smallest time investment.  Yet most Excel users do not even know what they are.

Have you ever faced these spreadsheet scenarios?

  • How can I use VLOOKUP to return all the matching items, not just the first?
  • How can I sort my information using a formula, so I don’t have to keep clicking the sort button?
  • How can I quickly create unique lists of items to use with my SUMIFS calculation?
  • How can I stop copying down formulas every time my source data changes.
  • How can I build a PivotTable-like report, but using formulas so I don’t have to click refresh ever again.

Well, I’m here to give you some good news. with dynamic arrays, all these can be achieved easily 🙂

Which other formulas does this work for?

This approach 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 a few things to be aware of:

  • All the sheets must be in a consistent layout and must stay that way. If one worksheet changes, the formula many not sum the correct cells.
  • Usually, formula 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.
  • Any sheets which are between the two tabs are included within the calculation.  Therefore, even though we cannot see them, hidden sheets are also included in the result.


Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Email Address * First Name *

By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Save

One thought on “Sum across multiple sheets in Excel

Leave a Reply

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