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...
=SUMPRODUCT(
SUMIFS(
INDIRECT("'"&SheetList&"'!f2:f2000"),
INDIRECT("'"&SheetList&"'!h2:h2000"),"CAF",
INDIRECT("'"&SheetList&"'!i2:i2000"),"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!
Menu Table | Contractors | Drawings | Equipment | Fees & Finance | Insurance | Office | QHSE | Build | Improvements | Repair & Maintenance | Payroll Expenses | Sales | Services & Consulting | Travel | Vehicles |
Helpdesk Menu: | _ED1 | _ED2 | _ED3 | _ED4 | _ED5 | _ED6 | _ED7 | _ED10 | _ED11 | _ED12 | _ED13 | _ED14 | _ED15 | ||
Renting Menu: | _ED1 | _ED2 | _ED3 | _ED4 | _ED5 | _ED6 | _ED7 | _ED9 | _ED10 | _ED11 | _ED12 | _ED13 | _ED14 | _ED15 | |
Haberdashery Menu: | _ED1 | _ED2 | _ED3 | _ED4 | _ED5 | _ED6 | _ED7 | _ED10 | _ED11 | _ED12 | _ED13 | _ED14 | _ED15 | ||
Studio Menu: | _ED1 | _ED2 | _ED3 | _ED4 | _ED5 | _ED6 | _ED7 | _ED8 | _ED9 | _ED10 | _ED11 | _ED12 | _ED13 | _ED14 | _ED15 |
Int Received Menu: |
I'm baaaaaaaaak!...
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
=SUMPRODUCT(
SUMIF(INDIRECT("'"&SheetList&"'!A2:A4"),A5,INDIRECT("'"&SheetList&"'!C2:C4")),
SUMIF(INDIRECT("'"&SheetList&"'!A2:A4"),A5,INDIRECT("'"&SheetList&"'!B2:B4")))