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]

Not sure why you said this at the beginning then?



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.

I didn't think I was going to get the 2D before the 1D! I use this equation in so many applications. @KRice and Peter thank you for the 2D solution as well! I will put this to good use. Thanks so much! @shaowu459 thanks for these solutions also, hopefully I get these soon. Great thread!

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.