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

 

[PrettyGood_Not Great]

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.

Hi Peter, here you go.

SORI_Labour_Report_M365_v1.3.0.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Source Data	Data Table	Mar-24	Apr-24	May-24	Jun-24	Jul-24	SUMIF INDEX MATCH Solution	Result Table	10	25	20	30	40	365 Soltuion	Result Table	10	25	20	30	40
2		10	0.56389732	0.97029344	0.84119858	0.23060284	0.79340637		Mar-24	1.004674	0.207005	0.741074	0.600935	1.184407		Mar-24
3		25	0.10793366	0.42365665	0.45145688	0.12426096	0.79783865		Apr-24	1.017182	0.672323	0.737344	1.10824	0.824173		Apr-24
4		20	0.20805109	0.48594148	0.47662379	0.99685046	0.2932109		May-24	1.814875	0.850343	1.29749	0.438088	0.70609		May-24
5		30	0.01371336	0.91582877	0.30998855	0.26859087	0.71700394		Jun-24	0.624037	0.556953	1.59303	0.539928	0.506879		Jun-24
6		40	0.46212571	0.19357642	0.0483556	0.083404	0.65086622		Jul-24	1.092388	0.980416	0.802922	1.552837	1.029699		Jul-24
7		10	0.44077663	0.04688847	0.97367675	0.39343427	0.29898129
8		25	0.09907098	0.2486659	0.39888605	0.43269169	0.18257712
9		20	0.53302276	0.25140238	0.8208664	0.59617942	0.50971132
10		30	0.58722206	0.19241094	0.12809979	0.27133664	0.83583355
11		40	0.72228168	0.63059611	0.65773464	0.42347469	0.37883283
Sheet1

Cell Formulas
Range	Formula
C2:G11	C2	=RAND()
J2:N6	J2	=SUMIF($B$2:$B$11,J$1,INDEX($C$2:$G$11,,MATCH($I2,$C$1:$G$1,0)))

 

[KRice]

That table structure is quite a bit different from the original. Here is one approach, although I'm not sure I'd go to the trouble just to have a 2-dimensional spill.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Source Data	Data Table	3/1/2024	4/1/2024	5/1/2024	6/1/2024	7/1/2024	SUMIF INDEX MATCH Solution	Result Table	10	25	20	30	40	365 Soltuion	Result Table	10	25	20	30	40
2		10	0.564	0.970	0.841	0.231	0.793		3/1/2024	1.005	0.207	0.741	0.601	1.184		3/1/2024	1.005	0.207	0.741	0.601	1.184
3		25	0.108	0.424	0.451	0.124	0.798		4/1/2024	1.017	0.672	0.737	1.108	0.824		4/1/2024	1.017	0.672	0.737	1.108	0.824
4		20	0.208	0.486	0.477	0.997	0.293		5/1/2024	1.815	0.850	1.297	0.438	0.706		5/1/2024	1.815	0.850	1.297	0.438	0.706
5		30	0.014	0.916	0.310	0.269	0.717		6/1/2024	0.624	0.557	1.593	0.540	0.507		6/1/2024	0.624	0.557	1.593	0.540	0.507
6		40	0.462	0.194	0.048	0.083	0.651		7/1/2024	1.092	0.980	0.803	1.553	1.030		7/1/2024	1.092	0.980	0.803	1.553	1.030
7		10	0.441	0.047	0.974	0.393	0.299
8		25	0.099	0.249	0.399	0.433	0.183
9		20	0.533	0.251	0.821	0.596	0.510
10		30	0.587	0.192	0.128	0.271	0.836
11		40	0.722	0.631	0.658	0.423	0.379
Sheet1

Cell Formulas
Range	Formula
J2:N6	J2	=SUMIF($B$2:$B$11,J$1,INDEX($C$2:$G$11,,MATCH($I2,$C$1:$G$1,0)))
Q2:U6	Q2	=MAKEARRAY(COUNTA(P2:P6),COUNTA(Q1:U1),LAMBDA(r,c,SUMPRODUCT(C2:G11,(B2:B11=INDEX(Q1:U1,,c))*(C1:G1=INDEX(P2:P6,r)))))
Dynamic array formulas.

 

[Peter_SSs]

I have also taken a MAKEARRAY approach (& simplified the sample data to make checking easier )

24 03 19.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Source Data	Data Table	Mar-24	Apr-24	May-24	Jun-24	Jul-24	SUMIF INDEX MATCH Solution	Result Table	10	25	20	30	40	365 Soltuion	Result Table	10	25	20	30	40
2		10	9	1	3	4	5		Mar-24	16	15	6	18	4		Mar-24	16	15	6	18	4
3		25	8	4	6	1	8		Apr-24	7	9	11	7	14		Apr-24	7	9	11	7	14
4		20	5	5	5	9	1		May-24	12	12	8	4	4		May-24	12	12	8	4	4
5		30	9	6	2	4	3		Jun-24	12	8	14	12	8		Jun-24	12	8	14	12	8
6		40	2	5	3	5	8		Jul-24	11	13	2	6	9		Jul-24	11	13	2	6	9
7		10	7	6	9	8	6
8		25	7	5	6	7	5
9		20	1	6	3	5	1
10		30	9	1	2	8	3
11		40	2	9	1	3	1
12
PGNG (2)

Cell Formulas
Range	Formula
J2:N6	J2	=SUMIF($B$2:$B$11,J$1,INDEX($C$2:$G$11,,MATCH($I2,$C$1:$G$1,0)))
Q2:U6	Q2	=MAKEARRAY(ROWS(P2:P6),COLUMNS(Q1:U1),LAMBDA(r,c,SUM(FILTER(INDEX(C2:G11,0,r),B2:B11=INDEX(Q1:U1,c),0))))
Dynamic array formulas.

 

[PrettyGood_Not Great]

SORI_Labour_Report_M365_v1.3.0.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Source Data	Data Table	Mar-24	Apr-24	May-24	Jun-24	Jul-24	SUMIF INDEX MATCH Solution	Result Table	10	25	20	30	40	365 Soltuion	Result Table	10	25	20	30	40
2		10	0.60476294	0.74056153	0.36144389	0.960507	0.16441565		Mar-24	1.386307	1.105354	0.756362	0.591526	1.070712		Mar-24
3		25	0.59868761	0.3122067	0.20060623	0.35025317	0.79864748		Apr-24	0.987887	0.716107	0.451311	1.175529	1.289386		Apr-24
4		20	0.09775988	0.17449372	0.48914761	0.43167799	0.66840129		May-24	1.211074	0.209814	0.758923	0.762681	0.960817		May-24
5		30	0.03447622	0.72195784	0.04251083	0.11139743	0.90724023		Jun-24	0.964608	0.566307	0.849076	0.951554	1.512473		Jun-24
6		40	0.9946253	0.70356035	0.01927836	0.9714408	0.79701032		Jul-24	0.776302	1.202163	1.624475	1.084408	1.289229		Jul-24
7		10	0.7815436	0.24732531	0.84962988	0.0041012	0.61188642
8		25	0.50666679	0.40390032	0.00920797	0.21605412	0.40351582
9		20	0.6586024	0.27681769	0.26977565	0.41739762	0.95607353
10		30	0.55704941	0.45357104	0.72017054	0.84015661	0.17716744
11		40	0.0760867	0.5858253	0.94153906	0.54103195	0.4922183
Sheet1

Cell Formulas
Range	Formula
C2:G11	C2	=RAND()
J2:N6	J2	=SUMIF($B$2:$B$11,J$1,INDEX($C$2:$G$11,,MATCH($I2,$C$1:$G$1,0)))

 

[PrettyGood_Not Great]

I have also taken a MAKEARRAY approach (& simplified the sample data to make checking easier )

24 03 19.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Source Data	Data Table	Mar-24	Apr-24	May-24	Jun-24	Jul-24	SUMIF INDEX MATCH Solution	Result Table	10	25	20	30	40	365 Soltuion	Result Table	10	25	20	30	40
2		10	9	1	3	4	5		Mar-24	16	15	6	18	4		Mar-24	16	15	6	18	4
3		25	8	4	6	1	8		Apr-24	7	9	11	7	14		Apr-24	7	9	11	7	14
4		20	5	5	5	9	1		May-24	12	12	8	4	4		May-24	12	12	8	4	4
5		30	9	6	2	4	3		Jun-24	12	8	14	12	8		Jun-24	12	8	14	12	8
6		40	2	5	3	5	8		Jul-24	11	13	2	6	9		Jul-24	11	13	2	6	9
7		10	7	6	9	8	6
8		25	7	5	6	7	5
9		20	1	6	3	5	1
10		30	9	1	2	8	3
11		40	2	9	1	3	1
12
PGNG (2)

Cell Formulas
Range	Formula
J2:N6	J2	=SUMIF($B$2:$B$11,J$1,INDEX($C$2:$G$11,,MATCH($I2,$C$1:$G$1,0)))
Q2:U6	Q2	=MAKEARRAY(ROWS(P2:P6),COLUMNS(Q1:U1),LAMBDA(r,c,SUM(FILTER(INDEX(C2:G11,0,r),B2:B11=INDEX(Q1:U1,c),0))))
Dynamic array formulas.

ok interesting. What can be done to prevent spilling in the horizontal. My sheet can spill down, but has multiple data arrays shoulder to shoulder so I can't spill to the right.

 

[shaowu459]

If PIVOTBY is available, you can try:

=PIVOTBY(TOCOL(C1:G1+C2:G11*0),TOCOL(B2:B11+C2:G11*0),TOCOL(C2:G11),SUM,,0,,0)

 

[shaowu459]

or you can try GROUPBY:

=TRANSPOSE(GROUPBY(B1:B11,C1:G11,SUM,3,0))

 

[PrettyGood_Not Great]

If PIVOTBY is available, you can try:

=PIVOTBY(TOCOL(C1:G1+C2:G11*0),TOCOL(B2:B11+C2:G11*0),TOCOL(C2:G11),SUM,,0,,0)

View attachment 108610

Unfortunately I don't have PIVOTBY. looks amazing though

 

[Peter_SSs]

ok interesting. What can be done to prevent spilling in the horizontal. My sheet can spill down, but has multiple data arrays shoulder to shoulder so I can't spill to the right.

Not sure why you said this at the beginning then?

that can spill down or possibly even spill in 2d

If you only want to spill down, then try ..

24 03 19.xlsm
	B	C	D	E	F	G								O	P	Q	R	S	T	U
1	Data Table	Mar-24	Apr-24	May-24	Jun-24	Jul-24								365 Soltuion	Result Table	10	25	20	30	40
2	10	9	1	3	4	5									Mar-24	16	15	6	18	4
3	25	8	4	6	1	8									Apr-24	7	9	11	7	14
4	20	5	5	5	9	1									May-24	12	12	8	4	4
5	30	9	6	2	4	3									Jun-24	12	8	14	12	8
6	40	2	5	3	5	8									Jul-24	11	13	2	6	9
7	10	7	6	9	8	6
8	25	7	5	6	7	5
9	20	1	6	3	5	1
10	30	9	1	2	8	3
11	40	2	9	1	3	1
PGNG (3)

Cell Formulas
Range	Formula
Q2:U6	Q2	=BYROW($P2:$P6,LAMBDA(r,SUMIF($B2:$B11,Q1,INDEX($C2:$G11,0,MATCH(r,$C1:$G1,0)))))
Dynamic array formulas.

 

[PrettyGood_Not Great]

or you can try GROUPBY:

=TRANSPOSE(GROUPBY(B1:B11,C1:G11,SUM,3,0))

View attachment 108613

Unfortunately I don't have this one either. Well noted though, looks like another really great solution!