Summing area of data across multiple tabs with multiple criteria-array and indirect?

[bladz1454]

Full disclosure this is a cross-forum thread (excelgure.ca) since I need to find a solution quickly!

https://www.excelguru.ca/forums/sho...abs-with-multiple-criteria-array-and-indirect

Hi Everyone, I'm spinning my wheels here between index match and sumproduct arrays but I think the indirect for multiple tabs is whats killing me. Here's the scenario, I have a GL data dumps (one tab for each month) and I need to map the GL account to the financial statement line items. Sounds simple but there are multiple 'stores' in each GL as well and for some reason when I use indirect its not working for the tabs (I'm using tab names instead of tables for the indirect reference). Another thing that I think always clouds my mind is I like to create formulas I can 1) Drag and 2) easily use in the future by adding data... I've also attached a sheet for reference - located in other forum since Mr Excel doesnt let you add files.

Thanks so much in advance!

Raw data in tabs (two tabs for two months as example):

[TABLE="class: cms_table, width: 400"]
<tbody>[TR]
[TD]Tab - January[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GL Account[/TD]
[TD]Store A[/TD]
[TD]Store B[/TD]
[TD]Store C[/TD]
[/TR]
[TR]
[TD]5011 · Apples[/TD]
[TD="align: right"]10.00[/TD]
[TD="align: right"]11.00[/TD]
[TD="align: right"]15.00[/TD]
[/TR]
[TR]
[TD]5012 · Bananas[/TD]
[TD="align: right"]20.00[/TD]
[TD="align: right"]12.00[/TD]
[TD="align: right"]16.00[/TD]
[/TR]
[TR]
[TD]5013 · Grapes[/TD]
[TD="align: right"]30.00[/TD]
[TD="align: right"]13.00[/TD]
[TD="align: right"]17.00[/TD]
[/TR]
[TR]
[TD]5014 · Water[/TD]
[TD="align: right"]40.00[/TD]
[TD="align: right"]14.00[/TD]
[TD="align: right"]18.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tab - February[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GL Account[/TD]
[TD]Store A[/TD]
[TD]Store B[/TD]
[TD]Store C[/TD]
[/TR]
[TR]
[TD]5011 · Apples[/TD]
[TD="align: right"]15.00[/TD]
[TD="align: right"]16.00[/TD]
[TD="align: right"]20.00[/TD]
[/TR]
[TR]
[TD]5012 · Bananas[/TD]
[TD="align: right"]25.00[/TD]
[TD="align: right"]17.00[/TD]
[TD="align: right"]21.00[/TD]
[/TR]
[TR]
[TD]5013 · Grapes[/TD]
[TD="align: right"]35.00[/TD]
[TD="align: right"]18.00[/TD]
[TD="align: right"]22.00[/TD]
[/TR]
[TR]
[TD]5014 · Water[/TD]
[TD="align: right"]45.00[/TD]
[TD="align: right"]19.00[/TD]
[TD="align: right"]23.00[/TD]
[/TR]
</tbody>[/TABLE]


Mapping from GL to Financials:
[TABLE="class: cms_table, width: 405"]
<tbody>[TR]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]GL Name[/TD]
[TD]Statement Name[/TD]
[/TR]
[TR]
[TD]5011 · Apples[/TD]
[TD]Revenue - Fruit[/TD]
[/TR]
[TR]
[TD]5012 · Bananas[/TD]
[TD]Revenue - Fruit[/TD]
[/TR]
[TR]
[TD]5013 · Grapes[/TD]
[TD]Revenue - Fruit[/TD]
[/TR]
[TR]
[TD]5014 · Water[/TD]
[TD]Revenue - Drinks[/TD]
[/TR]
</tbody>[/TABLE]


Final Product(s):

[TABLE="class: cms_table, width: 336"]
<tbody>[TR]
[TD]Financial Summary - January[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Store A[/TD]
[TD]Store B[/TD]
[TD]Store C[/TD]
[/TR]
[TR]
[TD]Revenue - Fruit[/TD]
[TD="align: right"]60.00[/TD]
[TD="align: right"]36.00[/TD]
[TD="align: right"]48.00[/TD]
[/TR]
[TR]
[TD]Revenue - Drinks[/TD]
[TD="align: right"]40.00[/TD]
[TD="align: right"]14.00[/TD]
[TD="align: right"]18.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Financial Summary - February[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Store A[/TD]
[TD]Store B[/TD]
[TD]Store C[/TD]
[/TR]
[TR]
[TD]Revenue - Fruit[/TD]
[TD="align: right"]75.00[/TD]
[TD="align: right"]51.00[/TD]
[TD="align: right"]63.00[/TD]
[/TR]
[TR]
[TD]Revenue - Drinks[/TD]
[TD="align: right"]45.00[/TD]
[TD="align: right"]19.00[/TD]
[TD="align: right"]23.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[Johnny C]

Not directly applicable but you may be able to chop it up. I've got a sheet with a variable number of sheets for projects. The list of projects that needs adding up is a dynamic range, on each sheet in cell Y2 is a value selected from a dropdown (a cost centre in this case) and this code adds up the value of cell F9 in each project sheet where the sheet has a particular cost centre selected in Y2 that matches the cost centre in the target sheet in $L$1:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&ListProjects&"'!"&CELL("address",F9)),INDIRECT("'"&ListProjects&"'!$Y$2"),$L$1))

it works because the array values are in a dynamic list that INDIRECT turns into an array

 

[bladz1454]

Not directly applicable but you may be able to chop it up. I've got a sheet with a variable number of sheets for projects. The list of projects that needs adding up is a dynamic range, on each sheet in cell Y2 is a value selected from a dropdown (a cost centre in this case) and this code adds up the value of cell F9 in each project sheet where the sheet has a particular cost centre selected in Y2 that matches the cost centre in the target sheet in $L$1:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&ListProjects&"'!"&CELL("address",F9)),INDIRECT("'"&ListProjects&"'!$Y$2"),$L$1))

it works because the array values are in a dynamic list that INDIRECT turns into an array

Thanks, but I think what I'm doing it a bit different in regards to reading multiple ranges.

Anyone else?

 

[bladz1454]

Sorry does anyone know if this might be easier if I just drag all the data into a single tab too?

 

[MARZIOTULLIO]

Try
Sheet

Finacials

B4=SUMPRODUCT((January!$A$2:$A$5<>January!$A$5)*(January!$B$1:$D$1=Financials!B$3)*(January!$B$2:$D$5)) copy across

B5=SUMPRODUCT((January!$A$2:$A$5=January!$A$5)*(January!$B$1:$D$1=Financials!B$3)*(January!$B$2:$D$5)) copy across

B10=SUMPRODUCT((February!$A$2:$A$5<>February!$A$5)*(February!$B$1:$D$1=Financials!B$9)*(February!$B$2:$D$5)) copy across

B11=SUMPRODUCT((February!$A$2:$A$5=February!$A$5)*(February!$B$1:$D$1=Financials!B$9)*(February!$B$2:$D$5)) copy across

 

[bladz1454]

Try
Sheet

Finacials

B4=SUMPRODUCT((January!$A$2:$A$5<>January!$A$5)*(January!$B$1:$D$1=Financials!B$3)*(January!$B$2:$D$5)) copy across

B5=SUMPRODUCT((January!$A$2:$A$5=January!$A$5)*(January!$B$1:$D$1=Financials!B$3)*(January!$B$2:$D$5)) copy across

B10=SUMPRODUCT((February!$A$2:$A$5<>February!$A$5)*(February!$B$1:$D$1=Financials!B$9)*(February!$B$2:$D$5)) copy across

B11=SUMPRODUCT((February!$A$2:$A$5=February!$A$5)*(February!$B$1:$D$1=Financials!B$9)*(February!$B$2:$D$5)) copy across

Haha yea that gets to the result but it's cheating. The reason for the mapping is because the real data Im' using have hundreds of GL accounts. You're solution can't handle more than 2 financial statement line items without referring to a map.

I like where your heads at, any chance you can do it using the mapping key?

 

[Johnny C]

You can't 'reverse lookup' the map from within a SUMPRODUCT like you want - that's an Access job.
You need a helper column in each sheet to do that with a VLOOKUP/OFFSET or Index/MATCH and a total for the stores in each row, I'd insert a column B and C in front of the data and before the stores so you know it's the same place each month (assuming if you open a new store in June you get a new column)
if you do them you could get away with a SUMIFS for each sheet
The easiest option is the one you identified - put it all into a single tab with a column for the month name and the total and helper column. You can easily slice and dice on that.

 

[bladz1454]

You can't 'reverse lookup' the map from within a SUMPRODUCT like you want - that's an Access job.
You need a helper column in each sheet to do that with a VLOOKUP/OFFSET or Index/MATCH and a total for the stores in each row, I'd insert a column B and C in front of the data and before the stores so you know it's the same place each month (assuming if you open a new store in June you get a new column)
if you do them you could get away with a SUMIFS for each sheet
The easiest option is the one you identified - put it all into a single tab with a column for the month name and the total and helper column. You can easily slice and dice on that.

Thanks Johnny! Yea that makes sense. Last night I started messing around with Power Query... really trying to figure this out with as minimal data manipulation as possible since we export tons of GLs all the time.

 

[Johnny C]

we export tons of GLs all the time.

Haha me too. Dunno what we did in a previous life to deserve that.

Unless the data gets too big (i.e. there's matches and countifs/Sumifs in it which slows it down too much), I always put it into a single block if I can, it just makes life easier in the long run.

These days I always try and use tables for blocks of data so formulae get copied down automatically. They do corrupt alas if they get too big and you monkey around with individual cells. We're in the process of upgrading to Excel 2016 and I'm looking to using tables and the Data Model to consolidate tables and update linked values without needing a lookup which I've read you can do but am buggered if I can find out how.