SUMIFS with merged cells and array formula

[hymced]

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 ?

 

[Fluff]

If you want to post data to the site please use one of the tools available here https://www.mrexcel.com/forum/about-board/508133-attachments.html
rather than the old HTMLMaker

 

[hymced]

example more readable here: https://codepen.io/hymced/pen/GLdOVX

 

[hymced]

oh okay

 

[hymced]

If you want to post data to the site please use one of the tools available here https://www.mrexcel.com/forum/about-board/508133-attachments.html
rather than the old HTMLMaker

In fact I tried the last version because the other 2 won't work :

- MrExcel HTML Maker 20170807: won't work because on a VBA error, not compatible with my Fr-Fr system, in mCreateHML, CLng("16.0") fails, in my case it requires CLng("16,0") is expected
- Forum Tools Add-In by RoryA: won't work either, error in mCreateHTML at this line:

If Intersect(rngHasArray, Cell.CurrentArray) Is Nothing Then

, Intersect fails with rngHasArray being Nothing in my specific example

I fixed the first one, here is, finally, the example:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1										1	2	3		1	2
2	Initial data									Intermediary Goals: any of these 3
3	A	9		A	9		3	A		0	3	A		A	A
4		8		0	8			A		1	3	A		A	A
5		7		0	7			A		2	3	A		A	A
6	B	6		B	6		6	B		0	6	B		B	B
7		5		0	5			B		1	6	B		B	B
8		4		0	4			B		2	6	B		B	B
9	C	3		C	3		9	C		0	9	C		C	C
10		2		0	2			C		1	9	C		C	C
11		1		0	1			C		2	9	C		C	C
12
13	Final Goal
14	A	24
15	B	15
16	C	6

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula K3 =G3 K4 =K3 K5 =K4 K6 =G6 K7 =K6 K8 =K7 K9 =G9 K10 =K9 K11 =K10 B14 =SUMIFS($B$3:$B$11,$O$3:$O$11,A14) B15 =SUMIFS($B$3:$B$11,$O$3:$O$11,A15) B16 =SUMIFS($B$3:$B$11,$O$3:$O$11,A16) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula G3:G11 {=IF(NOT(ISBLANK($A$3:$A$11)),ROW($A$3:$A$11),"")} H3:H11 {=IF($D$3:$D$11<>0,$D$3:$D$11,OFFSET($H$3:$H$11,-1,0,,))} N3:N11 {=IF($D$3:$D$11<>0,$D$3:$D$11,OFFSET($D$3:$D$11,-($J$3:$J$11),0,,))} O3:O11 {=INDEX($A$3:$A$11,$K$3:$K$11-ROW(A3)+1)} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[hymced]

I also found this: https://stackoverflow.com/a/42890056/2981328
it works with my example, but does not anymore if let's say C becomes A gain in criteria_range (column A)

 

[hymced]

FIY, I came to the conclusion that there might be no "cleaner" alternative than using the LOOKUP function (I was attempting to use the MATCH function)

See why here:

https://stackoverflow.com/questions/52504395/understanding-how-excel-processes-array-functions

 

[hymced]

in fact it it possible, my problem was not from the MATCH function but from the INDEX function, and there is an obscure way around it. Problem solved !

Book1
	A	B	C	D	E	F	G
1	Merged	Value	Temp Filter
2	A	18	A		A	114	114
3		17	A		B	57	57
4		16	A		C	0	0
5		15	A
6		14	A
7		13	A
8	B	12	B
9		11	B
10		10	B
11		9	B
12		8	B
13		7	B
14	A	6	A
15		5	A
16		4	A
17		3	A
18		2	A
19		1	A
Sheet1

Cell Formulas
Range	Formula
F2	{=SUM((INDEX($A$2:$A$19,N(IF({1},MATCH(ROW($A$2:$A$19),IF(NOT(ISBLANK($A$2:$A$19)),ROW($A$2:$A$19),""),1))))=$E2)*$B$2:$B$19)}
F3	{=SUM((INDEX($A$2:$A$19,N(IF({1},MATCH(ROW($A$2:$A$19),IF(NOT(ISBLANK($A$2:$A$19)),ROW($A$2:$A$19),""),1))))=$E3)*$B$2:$B$19)}
F4	{=SUM((INDEX($A$2:$A$19,N(IF({1},MATCH(ROW($A$2:$A$19),IF(NOT(ISBLANK($A$2:$A$19)),ROW($A$2:$A$19),""),1))))=$E4)*$B$2:$B$19)}
G2	{=SUMPRODUCT(--(INDEX($A$2:$A$19,N(IF({1},MATCH(ROW($A$2:$A$19),IF(NOT(ISBLANK($A$2:$A$19)),ROW($A$2:$A$19),""),1))))=$E2),$B$2:$B$19)}
G3	{=SUMPRODUCT(--(INDEX($A$2:$A$19,N(IF({1},MATCH(ROW($A$2:$A$19),IF(NOT(ISBLANK($A$2:$A$19)),ROW($A$2:$A$19),""),1))))=$E3),$B$2:$B$19)}
G4	{=SUMPRODUCT(--(INDEX($A$2:$A$19,N(IF({1},MATCH(ROW($A$2:$A$19),IF(NOT(ISBLANK($A$2:$A$19)),ROW($A$2:$A$19),""),1))))=$E4),$B$2:$B$19)}
Press CTRL+SHIFT+ENTER to enter array formulas.