Adjust formula for across worksheets

[Fin Fang Foom]

Hi everyone

I have this formula below it will sum across worksheets.

=SUMPRODUCT(SUMIF(INDIRECT("'"&$D$2:$D$4&"'!"&CELL("address",A1:B1)),"<>"))

However I would like A1 multiplies B1 so I tried to change the formula into this:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$D$2:$D$4&"'!"&CELL("address",A1*B1)),"<>"))

You notice I changed the colon with a multiplication sign.

CELL("address",A1*B1)),"<>"))

It does not work, is there a work around for this?

 

[Richard Schollar]

What cell do you want the Cell() function to point at? ie in which column/row?

 

[Fin Fang Foom]

What cell do you want the Cell() function to point at? ie in which column/row?

Hi RichardSchollar,

This what I'm trying to do but across worksheets.

{=SUM(A1:A10*B1:B10)}

 

[Richard Schollar]

Hmm, I'll be honest with you here, I don't know if you can do this using standard Excel functions (someone like Domenic would be able to give you a much more definitive answer). However, if you download the morefunc add-in (see Recommended Links and Add-Ins thread) then you can use the THREED function:

=SUM(THREED(Sheet1:Sheet3!A1:A10)*THREED(Sheet1:Sheet3!B1:B10))

Confirmed with Ctrl+Shift+Enter. You can expand the sheets as required.

 

[Aladin Akyurek]

1) Place the sheet names in D2:D4 in a horizontal range, say, in D2:F2.

2) Assuming that the sheet names are on a sheet named Results, define RowSpec as referring to:

=ROW(INDIRECT("'"&Results!$D$2&"'!A1:A10"))-ROW(INDIRECT("'"&Results!$D$2&"'!A1"))

Then invoke:

=SUMPRODUCT(
     N(OFFSET(INDIRECT("'"&$D$2:$F$2&"'!A1:A10"),RowSpec,0,1)),
     N(OFFSET(INDIRECT("'"&$D$2:$F$2&"'!B1:B10"),RowSpec,0,1)))

Note. This type of formulas are expensive and hard to maintain.

 

[Fin Fang Foom]

1) Place the sheet names in D2:D4 in a horizontal range, say, in D2:F2.

2) Assuming that the sheet names are on a sheet named Results, define RowSpec as referring to:

=ROW(INDIRECT("'"&Results!$D$2&"'!A1:A10"))-ROW(INDIRECT("'"&Results!$D$2&"'!A1"))

Then invoke:

=SUMPRODUCT(
     N(OFFSET(INDIRECT("'"&$D$2:$F$2&"'!A1:A10"),RowSpec,0,1)),
     N(OFFSET(INDIRECT("'"&$D$2:$F$2&"'!B1:B10"),RowSpec,0,1)))

Note. This type of formulas are expensive and hard to maintain.

It's perfect Aladin thank you so much!

Hi RichardSchollar

Thank you for a alternate solution.

I'm going to go with Aldain solution because I never could get the morefunc add-in to work properly on my computers.


Thank you for all your help guys.