Formula challenge - sum criteria
- Thread starter blcexcel
- Start date
blcexcel
Member
I'm running Office 365. This would need to be compatible for Excel 2017.
I'm unable to change the data format due to the large amount of data I have and system constraints.
I also want to stay away from array formulas because this spreadsheet will be used for a large user population and they would not know how to deal with array formulas.
I need to avoid volatile formulas because this will be a large file and formulas such as indirect would result in calculation times that would make the spreadsheet unusable.
I worked on a solution for this for half a day solid. I can get the formula to sum the data for the first month with the "x" but I don't know how to make it work for the range of months.
I'm unable to change the data format due to the large amount of data I have and system constraints.
I also want to stay away from array formulas because this spreadsheet will be used for a large user population and they would not know how to deal with array formulas.
I need to avoid volatile formulas because this will be a large file and formulas such as indirect would result in calculation times that would make the spreadsheet unusable.
I worked on a solution for this for half a day solid. I can get the formula to sum the data for the first month with the "x" but I don't know how to make it work for the range of months.
Chihiro
Excel Ninja
Without use of array formula, and with current set up... It will be rather complex formula at any rate.
You'll likely not have viable solution without data structure change. Though other's more versed in formula solution may be able to help.
You can always change data structure before publishing for general use. (I.E. Use Get & Transform to restructure your data without altering the original, then break connection if compatibility is an issue).
Using cross-tab as starting point of data analysis is very common mistake, and should be avoided. It creates more headache than the little benefit it affords (human readability). Cross-tab should be the end-result and not the starting point.
You'll likely not have viable solution without data structure change. Though other's more versed in formula solution may be able to help.
You can always change data structure before publishing for general use. (I.E. Use Get & Transform to restructure your data without altering the original, then break connection if compatibility is an issue).
Using cross-tab as starting point of data analysis is very common mistake, and should be avoided. It creates more headache than the little benefit it affords (human readability). Cross-tab should be the end-result and not the starting point.
p45cal
Well-Known Member
Your months at the top of the tables in tab1 and tab2 are strings (not real dates) but it would be better if they were real dates so that there'd be no question of muddling months from different years. I did this in the attached file ending 'a'.
You can get away with only changing the headers of the table in tab1 so that they match the headers of the table in tab2. I've done this in the attached file ending 'b'.
It's the same array-entered formula in both workbooks, in cell D11:
and copy down.
It could be more elegant, but it's as the first time I got the right results. Too lazy.
You can get away with only changing the headers of the table in tab1 so that they match the headers of the table in tab2. I've done this in the attached file ending 'b'.
It's the same array-entered formula in both workbooks, in cell D11:
Code:
=SUM(IF(ISNUMBER(MATCH('tab2'!$C$3:$N$3,IF(ISNUMBER(MATCH(IF(INDEX('tab1'!$B$7:$M$11,MATCH(B11,'tab1'!$A$7:$A$11,0),0)="X",'tab1'!$B$6:$M$6),'tab1'!$B$6:$M$6,0)),'tab1'!$B$6:$M$6),0)),INDEX('tab2'!$C$4:$N$28,MATCH(B11 & C11,'tab2'!$A$4:$A$28 & 'tab2'!$B$4:$B$28,0),0)))It could be more elegant, but it's as the first time I got the right results. Too lazy.
Last edited:
Or, try this non-array formula
In D11, enter formula and copied down :
=SUMPRODUCT(('tab2'!$A$4:$A$28=B11)*('tab2'!$B$4:$B$28=C11)*INDEX('tab2'!$C$4:$N$4,MATCH('tab1'!B$6,'tab2'!$C$3:$N$3,0)):'tab2'!$Q$28*(INDEX('tab1'!$B$7:$M$11,MATCH(B11,'tab1'!$A$7:$A$11,0),0)="X"))
Regards
Bosco
In D11, enter formula and copied down :
=SUMPRODUCT(('tab2'!$A$4:$A$28=B11)*('tab2'!$B$4:$B$28=C11)*INDEX('tab2'!$C$4:$N$4,MATCH('tab1'!B$6,'tab2'!$C$3:$N$3,0)):'tab2'!$Q$28*(INDEX('tab1'!$B$7:$M$11,MATCH(B11,'tab1'!$A$7:$A$11,0),0)="X"))
Regards
Bosco
SUMPRODUCT ( "tab2_array"*"tab1_array") required all array should be in same dimension, but your "tab1" table and "tab2" table appear in different width.
In order to combine 2 tables for calculation in SUMPRODUCT(), I use "tab1" table column width 12 for 2 tables array, that is the reason to use Q28.
So,
For easy understanding , it can also use OFFSET() instead of INDEX() and D11 become :
=SUMPRODUCT(('tab2'!$A$4:$A$28=B11)*('tab2'!$B$4:$B$28=C11)*OFFSET('tab2'!$B$4,0,MATCH('tab1'!B$6,'tab2'!$C$3:$N$3,0),25,12)*(INDEX('tab1'!$B$7:$M$11,MATCH(B11,'tab1'!$A$7:$A$11,0),0)="X"))
The result will be same as post #.10 formula
Regards
Bosco
Last edited:
Peter Bartholomew
Well-Known Member
I tackled this twice. Once for a dynamic array version of Excel and the other for traditional Excel. What I prefer in a solution is to write the formula once and have it spill to populate the table but two characteristics of the problem prevented that.
1. Aggregations are performed column by column so, unless one is planning to use MMULT, the month needs to be determined by a relative reference and the corresponding columns extracted from the 2D array.
2. Although SUMIFS accepts the 'climate' letters as an array, it expects range references for its value and criteria parameters. Given that we need to perform calculations on one or other, the climate is also determined by a relative reference allowing the use of arrays to filter the climate letter.
As a further observation, if one is planning to use arrays, rather than tables/lists, the associated indices must be absolutely regular and consistent between arrays/tables. The Tab1 table had and extra Sub=127 and some F and G rows were missing from the Tab2 table.
1. Aggregations are performed column by column so, unless one is planning to use MMULT, the month needs to be determined by a relative reference and the corresponding columns extracted from the 2D array.
2. Although SUMIFS accepts the 'climate' letters as an array, it expects range references for its value and criteria parameters. Given that we need to perform calculations on one or other, the climate is also determined by a relative reference allowing the use of arrays to filter the climate letter.
As a further observation, if one is planning to use arrays, rather than tables/lists, the associated indices must be absolutely regular and consistent between arrays/tables. The Tab1 table had and extra Sub=127 and some F and G rows were missing from the Tab2 table.
blcexcel
Member
Using some tips from everyone I was able to create a formula that is working. There is one improvement I would like to make. Currently the formula will work if month numbers are entered in row 5. This is the row users will enter data. They will be entering "X"s. If I don't get a a solution that will work for "X"s I will have to create formulas to the side of the spreadsheet to convert the "X"s to numbers and there are actually 48 columns where they can enter values. Thus, I would prefer the formula to be able to work if "X"s are entered instead of month numbers if anyone has input on that. Thanks in advance!!!