Two options...
1]
=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A2:A10"),D2,INDIRECT("'"&SheetList&"'!B2:B10")))
where SheetList refers to a range that lists the relevant sheet names, A2:B10 the relevant range in every sheet in SheetList, and D2 the criterion value.
2] Using the morefunc.xll add-in... . . .
Hi, Aladin or other Excel expert,
I'm rather new to Excel. How do you set up the range '&SheetList&', mentioned in method 1?
BTW, why are 2 levels of quotes required inside the INDIRECT parens?
Lets say that we have sheets named jan, feb, and mar.
Enter these names on a different sheet in A2:A4.
Select A2:A4, go to the Name Box on the Formula Bar, type SheetList, and hit enter.
Opening " and closing " are required by INDIRECT, a function which expects a string.
Opening ' and closing ' have to do with the fact that the sheet names would/can have spaces which Excel needs to ignore.
I have this implemented and it produces the correct results:
=SUMPRODUCT(SUMIF(INDIRECT("'"&RebSheetList&"'!G5:G30"),$E6,OFFSET(INDIRECT("'"&RebSheetList&"'!H5:U30"),0,MATCH(F$1,INDIRECT("'"&RebSheetFirst&"'!H4:U4"),0)-1)))
HOWEVER at the top of sheet to be summed I now want to include a conversion rate that is appied to each value on the respective sheet BEFORE summing the results into the master consolidated sheet. For example: I am summing expenses, with criteria, over multiple sheets and each sheet may be in a different currency therefore at the top of each column I have the currency rate which is to be applied to each value to consolidate into a single currency. I tried adding the following to the end of the above:
/SUMPRODUCT(SUMIF(INDIRECT("'"&RebSheetList&"'!G5:G30"),"Rate",OFFSET(INDIRECT("'"&RebSheetList&"'!H5:U30"),0,MATCH(H$1,INDIRECT("'"&RebSheetFirst&"'!H4:U4"),0)-1)))
But it takes the sum of ALL the conversion rates and then divides that into the result. Any ideas please?
Summary Sheet:
Nov-11 Dec-11
20% 20%
RevFut 0 484
Detail Sheet:
OutCode Nov-11 Dec-11
Rate 1.0000 1.0000
RevFut 0.00 423.28
RevFut 0.00 25.01
RevFut 0.00 35.28
RevFut 0.00 0.00
RevFut 0.00 0.35
0.00 483.92
Two options...
1]
=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A2:A10"),D2,INDIRECT("'"&SheetList&"'!B2:B10")))
where SheetList refers to a range that lists the relevant sheet names, A2:B10 the relevant range in every sheet in SheetList, and D2 the criterion value.
2] Using the morefunc.xll add-in...
=SUMPRODUCT(--(THREED(Sheet2:Sheet3!$A$2:$A$10)=D2),(THREED(Sheet2:Sheet3!$B$2:$B$10)))
where Sheet2 and Sheet3 are the relevant sheets while A2:B10 is the relevant range and D2 the criterion.