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

 

[Helpdesk]

Of course! Wonderful, thank you!
..now I can get on to fixing up the reporting functions...

 

[Helpdesk]

Ooop! maybe not, I just had a deeper look, (nice solution though)



Thank you for that, but it's not so much that I want to SUMIF multiple ranges, but that I want to restrict the SUMIF to multiple conditions,

So
=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!h2:h2000"),"CAF",INDIRECT("'"&SheetList&"'!f2:f2000")))
/11

Checks and SUMIF on 'CAF' trigger

OK so far, but I have to report CAF for 'Helpdesk' and CAF for 'Studio', separately, from a comm on pool of downloaded bank statements (yes, I should have separate bank accounts for each cost centre, but I don't, I'm cheap )

So what I'd like to do is to put a condition on the SUMIF, so that it only SUMIF's 'CAF' from column H, when there is ALSO the word 'Helpdesk' in the corresponding row, column I.

Then I can do similar for 'Studio', and have the separate resulting for each cost centre...

 

[Aladin Akyurek]

Ooop! maybe not, I just had a deeper look, (nice solution though)



Thank you for that, but it's not so much that I want to SUMIF multiple ranges, but that I want to restrict the SUMIF to multiple conditions,

So
=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!h2:h2000"),"CAF",INDIRECT("'"&SheetList&"'!f2:f2000")))
/11

Checks and SUMIF on 'CAF' trigger

OK so far, but I have to report CAF for 'Helpdesk' and CAF for 'Studio', separately, from a comm on pool of downloaded bank statements (yes, I should have separate bank accounts for each cost centre, but I don't, I'm cheap )

So what I'd like to do is to put a condition on the SUMIF, so that it only SUMIF's 'CAF' from column H, when there is ALSO the word 'Helpdesk' in the corresponding row, column I.

Then I can do similar for 'Studio', and have the separate resulting for each cost centre...

Forgat to rearrange the sum range:

=SUMPRODUCT(
    SUMIFS(
      INDIRECT("'"&SheetList&"'!f2:f2000"),
      INDIRECT("'"&SheetList&"'!h2:h2000"),"CAF",
      INDIRECT("'"&SheetList&"'!i2:i2000"),"Helpdesk"))

 

[Helpdesk]

A resounding success!

...Now to go through it all, check and re-check, bung in some helps and guidance and it's ready to give away!

Hmmm, may need a bit of help with the stepped validation I'm using in lieu of menus, as I'm hitting a character limit.

Thank you for your help, see you again soon!

 

[Aladin Akyurek]

A resounding success!

...Now to go through it all, check and re-check, bung in some helps and guidance and it's ready to give away!

Hmmm, may need a bit of help with the stepped validation I'm using in lieu of menus, as I'm hitting a character limit.

Thank you for your help, see you again soon!

You are welcome. Thanks for providing feedback.

 

[Helpdesk]

I'm baaaaaaaaak!

OK, the formula works great! The formula I use to nest Validated ranges to create a context-driven menu also works fine:
=IF(MATCH(J1983,_E)=5,_E05,IF(MATCH(J1983,_E)=6,_E06,IF(MATCH(J1983,_E)=7,_E07,IF(MATCH(J1983,_E)=8,_E08,IF(MATCH(J1983,_E)=9,_E09,IF(MATCH(J1983,_E)=10,_E10,IF(MATCH(J1983,_E)=13,_E13,IF(MATCH(J1983,_E)=14,_E14,_E01))))))))

...to an extent. The problem is that Excel will not accept any more nested IF's. This lot is fine for a context driven menu depending upon what value is in J1983 (the "_E" is a horizontal range of cost areas, and each "_E[n]" is a named range that is pulled to drive a context-sensitive validation list.

This is OK for a single enterprise, but not one that has multiple cost centres, and I want my cake and eat it!

So I thought of using =INDEX(TableMenu,MATCH(G45,TableMenu__V,0),MATCH(H45,TableMenu_H,0)) on the following:

[TABLE="width: 2311"]
<tbody>[TR]
[TD]Menu Table[/TD]
[TD]Contractors[/TD]
[TD]Drawings[/TD]
[TD]Equipment[/TD]
[TD]Fees & Finance[/TD]
[TD]Insurance[/TD]
[TD]Office[/TD]
[TD]QHSE[/TD]
[TD]Build[/TD]
[TD]Improvements[/TD]
[TD]Repair & Maintenance[/TD]
[TD]Payroll Expenses[/TD]
[TD]Sales[/TD]
[TD]Services & Consulting[/TD]
[TD]Travel[/TD]
[TD]Vehicles[/TD]
[/TR]
[TR]
[TD]Helpdesk Menu:[/TD]
[TD]_ED1[/TD]
[TD]_ED2[/TD]
[TD]_ED3[/TD]
[TD]_ED4[/TD]
[TD]_ED5[/TD]
[TD]_ED6[/TD]
[TD]_ED7[/TD]
[TD][/TD]
[TD][/TD]
[TD]_ED10[/TD]
[TD]_ED11[/TD]
[TD]_ED12[/TD]
[TD]_ED13[/TD]
[TD]_ED14[/TD]
[TD]_ED15[/TD]
[/TR]
[TR]
[TD]Renting Menu:[/TD]
[TD]_ED1[/TD]
[TD]_ED2[/TD]
[TD]_ED3[/TD]
[TD]_ED4[/TD]
[TD]_ED5[/TD]
[TD]_ED6[/TD]
[TD]_ED7[/TD]
[TD][/TD]
[TD]_ED9[/TD]
[TD]_ED10[/TD]
[TD]_ED11[/TD]
[TD]_ED12[/TD]
[TD]_ED13[/TD]
[TD]_ED14[/TD]
[TD]_ED15[/TD]
[/TR]
[TR]
[TD]Haberdashery Menu:[/TD]
[TD]_ED1[/TD]
[TD]_ED2[/TD]
[TD]_ED3[/TD]
[TD]_ED4[/TD]
[TD]_ED5[/TD]
[TD]_ED6[/TD]
[TD]_ED7[/TD]
[TD][/TD]
[TD][/TD]
[TD]_ED10[/TD]
[TD]_ED11[/TD]
[TD]_ED12[/TD]
[TD]_ED13[/TD]
[TD]_ED14[/TD]
[TD]_ED15[/TD]
[/TR]
[TR]
[TD]Studio Menu:[/TD]
[TD]_ED1[/TD]
[TD]_ED2[/TD]
[TD]_ED3[/TD]
[TD]_ED4[/TD]
[TD]_ED5[/TD]
[TD]_ED6[/TD]
[TD]_ED7[/TD]
[TD]_ED8[/TD]
[TD]_ED9[/TD]
[TD]_ED10[/TD]
[TD]_ED11[/TD]
[TD]_ED12[/TD]
[TD]_ED13[/TD]
[TD]_ED14[/TD]
[TD]_ED15[/TD]
[/TR]
[TR]
[TD]Int Received Menu:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Where+
TableMenu is the data area containing the names or named ranges to use as List Validation
G45 is an input cell
TableMenu__V is the named range of the labels to the left of the data, vertical
H45 is another input cell
TableMenu_H is the named range of the labels to the top of the data, horizontal


...works fine where the table is on the same sheet, but not from another sheet. As a cell formula [BUT NOT AS A LIST VALIDATION]. Sigh.

I could get around it by copying and pasting the table into each sheet that needs it and linking the values ABSOLUTE to the master table so that updates and changes flow through, but it's messy and prone to errors if the table size changes ...and I'm still left with trying to convince Excel that it's a sequential range haha..

arr, ...any ideas folks?

 

[Aladin Akyurek]

I'm baaaaaaaaak!...

It's hard to bring this in line with the subject of this thread, that is, conditionally summing/countimg across multiple sheets.

It's probably better to describe the problem you have in words (not in formulas) along with the desired results.

 

[poiroo]

Thanks all for contribution..., the version 1) works perfectly, but I have one concern. Now face to this issue: 50 sheets (3 different type of currency, but each sheet has cell w/ ex.rate to EUR). Is there a chance to modify the formula 1) to consider the ex rate as well? Thx

 

[Aladin Akyurek]

Thanks all for contribution..., the version 1) works perfectly, but I have one concern. Now face to this issue: 50 sheets (3 different type of currency, but each sheet has cell w/ ex.rate to EUR). Is there a chance to modify the formula 1) to consider the ex rate as well? Thx

Does the following set up help, provided you have the same lay-out?

=SUMPRODUCT(
  SUMIF(INDIRECT("'"&SheetList&"'!A2:A4"),A5,INDIRECT("'"&SheetList&"'!C2:C4")),
  SUMIF(INDIRECT("'"&SheetList&"'!A2:A4"),A5,INDIRECT("'"&SheetList&"'!B2:B4")))

B2:B4 is assumed to house "ex. rate to EUR".

 

[dmcgoo]

Hi all

A very helpful thread!

I have used the following code: =SUMPRODUCT(SUMIF(INDIRECT("'"&SheetLists&"'!Ac9:Ac2000"),CONCATENATE(F$1,$D2),INDIRECT("'"&SheetLists&"'!r9:r2000"))) which works fine if the "SheetLists" refers only to one or 2 cells which have sheet names in them (i.e. if I use A6:A7). Note that the concatenate is being used to avoid using SUMIFS.

However the number of worksheets is variable and hence I have set up SheetLists to refer to the following: =OFFSET('Master Data'!$A$6,0,0,COUNTA('Master Data'!$A$6:$A$300)). All this formula is doing is setting the range based on the number of sheetsnames which have been returned (via separate macro) to the Master data sheet. However when I use this offset function, the sumproduct function then shows a #ref! error. I tested the SheetLists offset range via a dropdown validation (to see if my formula had a data entry error in it) and it appears to work ok.

Can anyone assist with this?

Many thanks

D