Hello all,
I am trying to build an array function that would work as a SUMIFS function, but with merged cells in the criteria_range. No VBA allowed, no temp helper column allowed,
no LOOKPUP function allowed (as done here: https://chandoo.org/forum/threads/formula-challenge-021-sumif-in-merged-cells.11927/),
no INDIRECT function allowed (as done here: https://www.mrexcel.com/forum/excel-questions/101119-sumif-merged-cells.html)
In the example above, the initial data is in columns A:A and B:B from row 3:3 to 11:11, and just below, the expected results is in $A$14:$C$16
The intermediary goal would be to build any of the vector in J:J or K:K or L:L with an array formula. After that, its a piece of cake.
As you can see, I have found a way in H:H, but the array formula is self-referencing the range in H:H to retrieve the value above, and that is incompatible with the final goal to wrap the intermediary formula in another formula directly without a temp helper column (I am thinking of something like =SUMIFS(INTERMEDIARY_ARRAY_FORMULA_FUNCTION_OF($A$3:$A$11;$B$3:$B$11)) )
And now, I'm stuck.
Any genius idea ?
I am trying to build an array function that would work as a SUMIFS function, but with merged cells in the criteria_range. No VBA allowed, no temp helper column allowed,
no LOOKPUP function allowed (as done here: https://chandoo.org/forum/threads/formula-challenge-021-sumif-in-merged-cells.11927/),
no INDIRECT function allowed (as done here: https://www.mrexcel.com/forum/excel-questions/101119-sumif-merged-cells.html)
In the example above, the initial data is in columns A:A and B:B from row 3:3 to 11:11, and just below, the expected results is in $A$14:$C$16
The intermediary goal would be to build any of the vector in J:J or K:K or L:L with an array formula. After that, its a piece of cake.
As you can see, I have found a way in H:H, but the array formula is self-referencing the range in H:H to retrieve the value above, and that is incompatible with the final goal to wrap the intermediary formula in another formula directly without a temp helper column (I am thinking of something like =SUMIFS(INTERMEDIARY_ARRAY_FORMULA_FUNCTION_OF($A$3:$A$11;$B$3:$B$11)) )
And now, I'm stuck.
Any genius idea ?
Last edited: