SUMIF MULTIPLE SHEETS

[MikeMN]

I have used the sumif formula many times ofer the years.

However, I have always only refrences one sheet.

I am now tying to refrence several sheets.

But I am getting a #value error message.

Any thoughts if this can be done.

Thank you

Mike

 

[nagase]

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?

 

[Aladin Akyurek]

Hi, Aladin or other Excel expert,
I'm rather new to Excel. How do you set up the range '&SheetList&', mentioned in method 1?

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.

BTW, why are 2 levels of quotes required inside the INDIRECT parens?

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.

 

[justme]

I have the SheetList going, but don't understand the formula.

My summary page has a list of the items (ColumnA) I want to find in the other sheets. The other sheets list these items in column C. I want a count of all the times Column E has an X in it across all the sheets.

 

[alexcn]

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?

 

[Aladin Akyurek]

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?

Would you post a small bit of one those sheets and explain the rate thing relative to that sample?

 

[alexcn]

Of course, not at my laptop now but essentially first row is months, second row is the rates, 3rd and subsequent rows are the data items to be summed with category codes in the left. So if March column contains 10,000 for one and 100,000 for the over and the rate is 2, then I want the summary sheet to show 75,000 in the March summary column. Therefore every number is subject to the rate but only on its own sheet.

Remember am summing across multiple sheets. Thanks Aladdin as always. The way I had it before, it was summing all the rates and then dividing THAT into the sum of the values as opposed to creating a sum of the sum of the revalued numbers.

 

[alexcn]

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

 

[Aladin Akyurek]

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

Would you want to apply the formula just to this sample?

 

[alexcn]

Well I have only supplied the first few months but in essence there are 12 monthly columns with financial data underneath. If you look at my first formula I would use the RevFut criteria to then sumproduct the detail worksheets for the respective months (i.e. use offset to find the Dec-11 column and then sum 423.28 + 25.01 + 35.28 + 0.35) to get the total on the summary page (i.e. 483.92 but shown rounded up to 484)

However let's say the factor is not 1.0000 for Dec-11 (under the months on the detail) but 1.5927 (i.e. the FX rate for GBP to USD) then I would want 484 / 1.5927 for the Dec-11 month and 303.83 shown in the summary. Of course, if the rate had moved from Dec-11 to Jan-12 from 1.5927 to 1.6324 then I would want 1.6324 used for the conversion of the Jan-12 detail when bringing forward to the summary sheet.

HTH

 

[aggiesmurf]

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.

Option 1 worked like a champ and was EXACTLY what I needed!! Thanks a million!!!