SumIfs with Index Match

[montanaaggie]

I can make my formula work (B16) with SUMIFS, but because the columns may change in time (Labor in column C, could someday be in column E), I would prefer to grab a whole table instead of a single column. As you can see in the formula in Row 16 I want to sum when a there's a "YES" (COLUMN E could move someday too) and it matches the Well Name (Won't move) and Cost Category (Won't move).

 

[Asbestos_Jen]

If you set the data up as a table, then the column references will stick even if they are rearranged.

Book1
	A	B	C	D	E
1	Well Name	Date	Labor	Water	Yes/No
2	Well 1		500	50	Yes
3	Well 1		700	60	Yes
4	Well 1		300	70	Yes
5	Well 1		800	100	No
6	Well 1		100	15	Yes
7	Well 2		5	65	Yes
8	Well 2		7	75	No
9	Well 2		3	85	No
10	Well 2		8	115	Yes
11	Well 2		1	30	Yes
12
13
14
15		Labor	Water
16	Well 1	1600	195
17	Well 2	14	210
Sheet1

Cell Formulas
Range	Formula
D7:D11	D7	=D2+15
B16:B17	B16	=SUMIFS(Table1[Labor],Table1[Well Name],$A16,Table1[Yes/No],"Yes")
C16:C17	C16	=SUMIFS(Table1[Water],Table1[Well Name],$A16,Table1[Yes/No],"Yes")

 

[MARZIOTULLIO]

Book1
	A	B	C	D	E
1	Well Name	Date	Labor	Water	Yes/No
2	Well 1		500	50	Yes
3	Well 1		700	60	Yes
4	Well 1		300	70	Yes
5	Well 1		800	100	No
6	Well 1		100	15	Yes
7	Well 2		5	65	Yes
8	Well 2		7	75	No
9	Well 2		3	85	No
10	Well 2		8	115	Yes
11	Well 2		1	30	Yes
12
13
14
15		Labor	Water
16	Well 1	1600	195
17	Well 2	14	210
Sheet1

Cell Formulas
Range	Formula
D7:D11	D7	=D2+15
B16:E17	B16	=IF($A16<>"",IFERROR(SUMIFS(INDEX($C$2:$E$11,,MATCH(B$15,$C$1:$E$1,0)),$A$2:$A$11,$A16,$E$2:$E$11,"yes"),""),"")

copy across and down

 

[montanaaggie]

Thanks so much,, that sumifs index match worked well for what I needed

 

[MARZIOTULLIO]

You are welcome