Dynamic 365 solution for SUMIF INDEX MATCH

[PrettyGood_Not Great]

Hi, I am wondering if there is a go to 365 equation that can spill down or possibly even spill in 2d that can replace an old 2016 staple.

SUMIF(Sheet1!Vertical Criteria Range,Vertical Criteria,INDEX(Sheet1!Sum Range,0,XMATCH(Horizontal Criteria,Sheet1!Horizontal Criteria Range,0))

If more information is required I can provide, however I am looking at this as a general case. I use this equation in many different places.

Thank you

 

[Eric W]

I'm not sure I'd recommend this, but here's one option:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Vertical Criteria	Horizontal Criteria	Old	New		Criteria	A	B	C	D	E	F	G
2	Alaska	A	111	111		Alaska	34	18	45	26	25	4	46
3	Ohio	C	45	45		Hawaii	15	42	50	16	42	22	10
4	Hawaii	E	61	61		Alaska	11	7	2	44	38	26	48
5	Alaska	G	167	167		Ohio	49	31	40	39	43	47	45
6	Maine	B	90	90		Maine	29	47	27	18	21	32	15
7						Maine	25	43	43	30	10	33	1
8						Ohio	13	13	5	3	7	27	16
9						Alaska	39	47	7	40	36	4	26
10						Alaska	27	35	20	36	33	34	47
11						Hawaii	6	11	21	13	19	25	5
Sheet13

Cell Formulas
Range	Formula
D2:D6	D2	=INDEX(SUMIF(F2:F11,UNIQUE(F2:F11),OFFSET(G2:M11,0,SEQUENCE(,COLUMNS(G2:M11),0),,1)),MATCH(A2:A6,UNIQUE(F2:F11),0),MATCH(B2:B6,G1:M1,0))
C2:C6	C2	=SUMIF($F$2:$F$11,A2,INDEX($G$2:$M$11,0,XMATCH(B2,$G$1:$M$1,0)))
Dynamic array formulas.

It actually calculates every possible combination of criteria and column, then uses the given criteria as an index to that table.

 

[Peter_SSs]

You could use basically the same formula but have it spill down using BYROW

24 01 29.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Vertical Criteria	Horizontal Criteria	Old	New		Criteria	A	B	C	D	E	F	G
2	Alaska	A	111	111		Alaska	34	18	45	26	25	4	46
3	Ohio	C	45	45		Hawaii	15	42	50	16	42	22	10
4	Hawaii	E	61	61		Alaska	11	7	2	44	38	26	48
5	Alaska	G	167	167		Ohio	49	31	40	39	43	47	45
6	Maine	B	90	90		Maine	29	47	27	18	21	32	15
7						Maine	25	43	43	30	10	33	1
8						Ohio	13	13	5	3	7	27	16
9						Alaska	39	47	7	40	36	4	26
10						Alaska	27	35	20	36	33	34	47
11						Hawaii	6	11	21	13	19	25	5
Spill SUMIF

Cell Formulas
Range	Formula
D2:D6	D2	=BYROW(A2:B6,LAMBDA(r,SUMIF(F2:F11,TAKE(r,,1),INDEX(G2:M11,0,XMATCH(TAKE(r,,-1),G1:M1)))))
C2:C6	C2	=SUMIF($F$2:$F$11,A2,INDEX($G$2:$M$11,0,XMATCH(B2,$G$1:$M$1,0)))
Dynamic array formulas.

You could also spill SUMPRODUCT in a similar way but if the data is very large it may be a bit more sluggish.

=BYROW(A2:B6,LAMBDA(r,SUMPRODUCT((F2:F11=TAKE(r,,1))*(G1:M1=TAKE(r,,-1)),G2:M11)))

 

[KRice]

I like the BYROW suggestion here...I suppose you could also SUM results from some nested FILTER functions to achieve the same, shown in E2:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Vertical Criteria	Horizontal Criteria	Old	New	New2	Criteria	A	B	C	D	E	F	G
2	Alaska	A	111	111	111	Alaska	34	18	45	26	25	4	46
3	Ohio	C	45	45	45	Hawaii	15	42	50	16	42	22	10
4	Hawaii	E	61	61	61	Alaska	11	7	2	44	38	26	48
5	Alaska	G	167	167	167	Ohio	49	31	40	39	43	47	45
6	Maine	B	90	90	90	Maine	29	47	27	18	21	32	15
7						Maine	25	43	43	30	10	33	1
8						Ohio	13	13	5	3	7	27	16
9						Alaska	39	47	7	40	36	4	26
10						Alaska	27	35	20	36	33	34	47
11						Hawaii	6	11	21	13	19	25	5
Sheet3

Cell Formulas
Range	Formula
D2:D6	D2	=BYROW(A2:B6,LAMBDA(r,SUMIF(F2:F11,TAKE(r,,1),INDEX(G2:M11,0,XMATCH(TAKE(r,,-1),G1:M1)))))
E2:E6	E2	=BYROW(A2:B6,LAMBDA(r,SUM(FILTER(FILTER(G2:M11,F2:F11=TAKE(r,,1)),G1:M1=TAKE(r,,-1)))))
C2:C6	C2	=SUMIF($F$2:$F$11,A2,INDEX($G$2:$M$11,0,XMATCH(B2,$G$1:$M$1,0)))
Dynamic array formulas.

 

[Peter_SSs]

I suppose you could also SUM results from some nested FILTER functions to achieve the same,

If the data can be large, I have found sometimes FILTER/FILTER can be a bit slower than SUMIF(S). Also, since this was a general question about method rather than for this specific data, your suggestion might need something more in case the criteria is not met. SUMIF(S) would simply return 0 but your FILTER formula would not.

 

[KRice]

...your suggestion might need something more in case the criteria is not met. SUMIF(S) would simply return 0 but your FILTER formula would not.

I agree with most of your points, Peter, but doesn't the same issue exist with the SUMIFS solutions?...not the SUMIFS function specifically, but with INDEX/XMATCH used to pass an array to SUMIFS. If XMATCH finds nothing, an error is generated and passed to SUMIFS, which in turn throws an #N/A error. It seems that any of these solutions might need additional work to address a no-match scenario?

 

[Peter_SSs]

but doesn't the same issue exist with the SUMIFS solutions?.

Sort of. To be honest I had only really considered if the vertical criteria was not found. My suggestion does return #N/A if the horizontal condition is not found, but that is what the original construct in post 1 does too & the OP was looking for a spill option to replace that. (Did I get out of that without too much egg on my face? )

 

[KRice]

Did I get out of that without too much egg on my face?

Absolutely spotless... Yeah, the vertical no-match condition is okay with SUMIFS and problematic for FILTER, while both have issues with the horizontal no-match scenario.

 

[PrettyGood_Not Great]

I like the BYROW suggestion here...I suppose you could also SUM results from some nested FILTER functions to achieve the same, shown in E2:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Vertical Criteria	Horizontal Criteria	Old	New	New2	Criteria	A	B	C	D	E	F	G
2	Alaska	A	111	111	111	Alaska	34	18	45	26	25	4	46
3	Ohio	C	45	45	45	Hawaii	15	42	50	16	42	22	10
4	Hawaii	E	61	61	61	Alaska	11	7	2	44	38	26	48
5	Alaska	G	167	167	167	Ohio	49	31	40	39	43	47	45
6	Maine	B	90	90	90	Maine	29	47	27	18	21	32	15
7						Maine	25	43	43	30	10	33	1
8						Ohio	13	13	5	3	7	27	16
9						Alaska	39	47	7	40	36	4	26
10						Alaska	27	35	20	36	33	34	47
11						Hawaii	6	11	21	13	19	25	5
Sheet3

Cell Formulas
Range	Formula
D2:D6	D2	=BYROW(A2:B6,LAMBDA(r,SUMIF(F2:F11,TAKE(r,,1),INDEX(G2:M11,0,XMATCH(TAKE(r,,-1),G1:M1)))))
E2:E6	E2	=BYROW(A2:B6,LAMBDA(r,SUM(FILTER(FILTER(G2:M11,F2:F11=TAKE(r,,1)),G1:M1=TAKE(r,,-1)))))
C2:C6	C2	=SUMIF($F$2:$F$11,A2,INDEX($G$2:$M$11,0,XMATCH(B2,$G$1:$M$1,0)))
Dynamic array formulas.

Hi Guys, thanks for the discussion. I see that I wasn't specific enough for my problem. These solutions would work for my case except for one key difference.

The Source array, and return array are actually a 1:1 transposition of each other (this would have been helpful I know), the dimensions of the source data and return array will not be the same of course. So if we have two criteria A and B. The source data has A vertically and B horizontally, and then the return array has A horizontally and B vertically. The SUMIF INDEX ,, MATCH pulls this off well, however it doesn't seem to spill regardless of what I feed into it.

 

[Peter_SSs]

Could we have some smallish sample data (with the expected results filled in manually) with XL2BB and explain again in relation to that sample data? That way we can be sure we are working with the layout and type of data you are, rather that guessing.