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.

 

[Aladin Akyurek]

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.

1.

="Production YTD "&TEXT(SUMPRODUCT(--(MOD(COLUMN(D67:AV67)-COLUMN(D67),4)=0),
        D67:AV67),"$ ##0.00")

2.

Create two new sheets, name them First and Last, place all of the relevant sheets between these two, and invoke:

=SUM(First:Last!B8)

 

[Marcelo Branco]

I think this can also work for number 2

=SUMPRODUCT(N(INDIRECT("'"&ROW(A1:A15)&"'!B8")))

M.

 

[Aladin Akyurek]

I think this can also work for number 2

=SUMPRODUCT(N(INDIRECT("'"&ROW(A1:A15)&"'!B8")))

M.

Costly. Becomes more so alas if we would like to avoid the consequences of inserting rows involving the A1:A15 range...

=SUMPRODUCT(N(INDIRECT("'"&ROW(INDIRECT("1:15"))&"'!B8")))

 

[Marcelo Branco]

Costly. Becomes more so alas if we would like to avoid the consequences of inserting rows involving the A1:A15 range...

=SUMPRODUCT(N(INDIRECT("'"&ROW(INDIRECT("1:15"))&"'!B8")))

Ok, but is possible to create a named range Sheetlist containing the names of the sheets and

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

(Not soooo costly with only 15 worksheets...)

M.

 

[MAP]

Dear Experts, I thank you for your suggestions. I truly appreciate the help you guys provide to us.

 

[Marcelo Branco]

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.

 

[MAP]

Fantastic, Marcelo! Thank you for discovering a new way to do the SUM. Thank you for sharing

 

[Marcelo Branco]

Fantastic, Marcelo! Thank you for discovering a new way to do the SUM. Thank you for sharing

My pleasure!

M.

 

[T. Valko]

Costly. Becomes more so alas if we would like to avoid the consequences of inserting rows involving the A1:A15 range...

=SUMPRODUCT(N(INDIRECT("'"&ROW(INDIRECT("1:15"))&"'!B8")))

Ok, but is possible to create a named range Sheetlist containing the names of the sheets and

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

(Not soooo costly with only 15 worksheets...)

M.

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")))