SUMIFS INDEX and MATCH

[atearth]

Hi all,

I'm trying to use SUMIFS INDEX and MATCH but the answer I get is wrong.
Anyone know what the answer is to a problem I have?
Please see the image below.
Thank you in advance

=SUMIFS(INDEX($F$15:$M$18,0,MATCH(F$24,$F$14:$M$14,0)),$D$15:$D$18,$D25)

 

[awoohaw]

Here is another version using OFFSET and SUM.
The OFFSET method does not use any array/matrix multiplication.
The index match above is also listed.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
13
14						Jan 23	Jan 23	Jan 23	Jan 23	Jan 23	Jan 23	Feb 23	Feb 23	Feb 23	Feb 23	Feb 23
15				100		450	1	19	20	2	3	450	27	4	5	6
16				101		7	675	21	22	8	9	10	28	675	11	12
17				102		13	14	23	24	900	15	16	29	17	900	18
18				103		226	227	25	26	228	229	221	30	222	223	224
19
20				USING: OFFSET		Jan 23	2/1/2023
21				100		495	492
22				101		742	736
23				102		989	980
24				103		961	920
25
26
27
28				USING INDEX MATCH		Jan 23	2/1/2023
29				100		495	492
30				101		742	736
31				102		989	980
32				103		961	920
sumproduct on grid

Cell Formulas
Range	Formula
F21:G24	F21	=SUM(OFFSET($F$15,MATCH($D21,$D$15:$D$18,0)-1,MATCH(MONTH(F$20),MONTH($F$14:$P$14),0)-1,1,SUM(--(MONTH(F$20)=MONTH($F$14:$P$14)))))
F29:G32	F29	=SUM(INDEX($F$15:$P$18,MATCH($D29,$D$15:$D$18,0),1): INDEX($F$15:$P$18,MATCH($D29,$D$15:$D$18,0),COLUMNS($F$14:$P$14)) *(--MONTH(F$28)=MONTH($F$14:$P$14)))

 

[AhoyNC]

OFFSET is a volatile function and will probably slow down your sheet.

 

[awoohaw]

OFFSET is a volatile function and will probably slow down your sheet.

just as bad as INDIRECT, I learned today! Thanks!

 

[atearth]

Thank you for the formulas

 

[AhoyNC]

In looking at how you have your data set up, if your WBS codes are always going to be in the same order then you could use SUMIFS as below.
Drag formula down and across.

Book1
	D	E	F	G	H	I	J
1
2	WBS Codes	1/23/2023	1/23/2023	1/23/2023	1/23/2023	2/23/2023	2/23/2023
3	100	450				450
4	101		675				675
5	102			900			900
6	103	225	225	225	225	225	225
7
8
9
10	WBS Codes	1/23/2023	2/23/2023
11	100	450	450
12	101	675	675
13	102	900	900
14	103	900	450
Sheet2

Cell Formulas
Range	Formula
E11:F14	E11	=SUMIFS($E3:$J3,$E$2:$J$2,E$10)