In MS Excel when adding a cell in multiple sheets...?

Question

Say I've renamed the sheets so would I say:

=SUM(Sheet2:Sheet4!a1) or =SUM(Name2:Name4!a1)

I've tried both and neither work...help...

Answer 1

If you rename your sheets, the formulas should adjust automatically.

=SUM(Name2:Name4!A1) should work just fine.  

The trick, when summing across multiple sheets this way, is that excel doesn't read (sheet2:sheet4!A1) as "sum A1 from sheet2, sheet3, and sheet4" it reads it as "sum A1 from sheet2 and sheet4 and from any sheet located between them"  So if you're sheet tabs are arranged in this order: sheet2, SummarySheet, sheet4, sheet3, then your SUM is going to include the summarysheet but not sheet3.

Referring to each sheet specifically avoids this potential problem.

=SUM(Name1!A1, Name2!A1, Name3!A1,)

Answer 2

If the sheets are named Sheet1, Sheet2, etc, then the formula would be:

=SUM(Sheet2!A1,Sheet3!A1,Sheet4!A1)

If the sheets are named Red, White, and Blue then the formula would be:

=SUM(Red!A1,White!A1,Blue!A1)

Note:  If you change the sheet names after you have entered your formula, Excel will automatically update the formula to include the new sheet name(s).