easy way to SUM multiple cells

[MAP]

Experts, I want to know from you if there is a better way to do a =SUM.

(A) consider the following situation I have in one of my worksheets where I am trying to add various cells in the same row:
="Production YTD "&TEXT(SUM(D67,H67,L67,P67,T67,X67,AB67,AF67,AJ67,AN67,AR67,AV67),"$ ##0.00")

(B) or the following situation where I am attempting to add the same cell in different worksheets:
='1'!B8+'2'!B8+'3'!B8+'4'!B8+'5'!B8+'6'!B8+'7'!B8+'8'!B8+'9'!B8+'10'!B8+'11'!B8+'12'!B8+'13'!B8+'14'!B8+'15'!B8

Thank you for sharing your Excel talents.

 

[Marcelo Branco]

If there are "only" 15 sheets instead of using the expensive expression:

ROW(INDIRECT("1:15"))

Just use an array constant and give it a defined name.

Name: Sheets
Refers to: {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}

Then:

=SUMPRODUCT(N(INDIRECT("'"&Sheets&"'!B8")))

Good point!

Thank you.

M.

 

[T. Valko]

Good point!

Thank you.

M.

 

[Michaels]

Just for the records:
So, in Sheet2, you can use to add all sheets
=SUM('*'!B8)+B8

Note: if later a new sheet is created the formula doesn't update automatically. You have to re-enter the formula.

M.

This is amazing Marcelo. Thanks for the formula.

 

[Marcelo Branco]

This is amazing Marcelo. Thanks for the formula.

You are welcome!

M.

 

[Robert Mika]

Just for the records:
A different solution that i saw on a book that for me is something new.

Suppose your workbook has 4 sheets - Sheet1, Sheet2, Sheet3 and Sheet4.

In Sheet1 enter in some cell
=SUM('*'!B8)

Excel interprets the asterisk as "all sheets except this one" and when you press Enter converts the formula to
=SUM(Sheet2:Sheet4!B8)

If you insert the formula on a sheet is in the middle of other sheets, say in Sheet2, the formula becomes
=SUM(Sheet1!B8,Sheet3:Sheet4!B8)

So, in Sheet2, you can use to add all sheets
=SUM('*'!B8)+B8

Note: if later a new sheet is created the formula doesn't update automatically. You have to re-enter the formula.

M.

Which book was that?

 

[Marcelo Branco]

Which book was that?

Excel 2007 Tips & Tricks
John Walkenbach

(Pages 174/5 --> Summing Across Sheets)

M.

 

[T. Valko]

Excel 2007 Tips & Tricks
John Walkenbach

(Pages 174/5 --> Summing Across Sheets)

M.

This doesn't work for me in Excel 2002.

If the file has 3 sheets and you enter the formula on Sheet1 it works OK:

=SUM(Sheet2:Sheet3!A1)

On Sheet2 the formula gets entered as:

=SUM(Sheet1!A,Sheet3!A1)

And this returns the #NAME? error.

And it works OK on Sheet3:

=SUM(Sheet1:Sheet2!A1)

It works OK in Excel 2007.

 

[Marcelo Branco]

On Sheet2 the formula gets entered as:

=SUM(Sheet1!A,Sheet3!A1)

And this returns the #NAME? error.

Is that a typo or the formula really gets entered A instead of A1?

M.

 

[T. Valko]

Is that a typo or the formula really gets entered A instead of A1?

M.

Not a typo.

In Excel 2002 when I type the formula I get to this point:

=SUM('*'!

And as soon as I type in the "A" (for column A) the formula converts to:

=SUM(Sheet1!A,Sheet3!A

In Excel 2007 I type in the entire formula:

=SUM('*'!A1)

It converts to:

=SUM(Sheet1!A1,Sheet3!A1)

AFTER I hit Enter.

 

[Marcelo Branco]

Just out of curiosity. If you type from right to left
)
1)
A1)
!A1)
and so on

Or if you copy+paste

M.