Sumif total accross columns using an achored reference

[castilian07]

I am looking for a moving total based on a criteria in my table from January through December depending on what month I choose. If I choose February I want it to give me Jan and Feb when I change to March, I want it to give me Jan, Feb, & Mar and so on. I have attached my example. sumif with offset works when only one row matches the criteria. I have several rolls matching the criteria. These formula's work but they are not giving me the ytd on my multiple row "OE" in column G criteria. I am looking for the OE for Jan, Feb, Mar, and so on based on what month I choose.

Basic Criteria using fixed month		=SUMIF(G$5:$G19,$G$18,$J$5:$J$19)
Moving Monthly Criteria		=SUMIF($G$5:$T$20,$H$24,INDEX(G5:T5,0,MATCH(H25,G5:T5,0)))

<colgroup><col><col><col><col span="5"><col></colgroup><tbody>
</tbody>

YTD total based on moving monthly criteria and Criteria in column H. Offset is being used to achor the cell reference =SUM(OFFSET(H4,MATCH(G28,H5:H20,0),,,MATCH(G27,H5:T5))) <colgroup><col><col><col><col span="5"><col span="2"></colgroup><tbody> </tbody>

<tbody>
</tbody>

4 G H I J K L M N O P Q R S T 5 Jan-17 Feb-17 Mar-17 Apr-17 May-17 Jun-17 Jul-17 Aug-17 Sep-17 Oct-17 Nov-17 Dec-17 6 OE (-) Utilities (127) (127) (127) (128) (129) (129) (129) (131) (132) (129) (131) (132) 7 (-) Labor Costs (328) (328) (328) (333) (335) (335) (335) (340) (342) (335) (340) (342) 8 OE (-) Plant Maintenance (68) (68) (68) (69) (69) (69) (69) (70) (70) (69) (70) (70) 9 OE (-) Lease Engine Program (333) (333) - - - (340) - - - - - - 10 OE (-) Outside Services (24) (24) (24) (24) (24) (24) (24) (25) (25) (24) (25) (25) 11 (-) Plant Office & Admin (27) (27) (27) (28) (28) (28) (28) (28) (28) (28) (28) (28) 12 OE (-) Chemicals (26) (26) (26) (27) (27) (27) (27) (27) (28) (27) (27) (28) 13 OE (-) Other Operations Expenses (36) (36) (36) (37) (37) (37) (37) (37) (38) (37) (37) (38) 14 OE (-) Insurance - - - - - (158) - - - - - - 15 OE (-) Property Tax - - (137) (137) - - (137) (137) - (137) (137) - 16 (-) Professional Services (26) (26) (26) (27) (27) (27) (27) (27) (27) (27) (27) (27) 17 OE (-) O&M Fee (63) (63) (63) (64) (65) (65) (65) (66) (66) (65) (66) (66) 18 OE (-) Management Fees (69) (69) (69) (70) (70) (70) (70) (71) (71) (70) (71) (71) 19 (19) (19) (19) (19) (19) (19) (19) (19) (20) (19) (19) (20) <colgroup><col><col><col><col><col><col span="5"><col span="5"></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[NiMip]

I think this works, but I might be missing what you mean by the sentence "These formula's work but they are not giving me the ytd on my multiple row "OE" in column G criteria"

Formula is =SUM(IFERROR((G$6:$G19=$G$18)*OFFSET($I$6:$I$19,,,,MATCH($I$2,TEXT($I$5:$T$5,"MMM"),0)),0))
Requires CONTROL+SHIFT+ENTER to work, should see {} appear around the formula if you've done it properly.

	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2		Month:	Jun
3		Result:	-3938
4
5			Jan-17	Feb-17	Mar-17	Apr-17	May-17	Jun-17	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17
6	OE	(-) Utilities	-127	-127	-127	-128	-129	-129	-129	-131	-132	-129	-131	-132
7		(-) Labor Costs	-328	-328	-328	-333	-335	-335	-335	-340	-342	-335	-340	-342
8	OE	(-) Plant Maintenance	-68	-68	-68	-69	-69	-69	-69	-70	-70	-69	-70	-70
9	OE	(-) Lease Engine Program	-333	-333	-	-	-	-340	-	-	-	-	-	-
10	OE	(-) Outside Services	-24	-24	-24	-24	-24	-24	-24	-25	-25	-24	-25	-25
11		(-) Plant Office & Admin	-27	-27	-27	-28	-28	-28	-28	-28	-28	-28	-28	-28
12	OE	(-) Chemicals	-26	-26	-26	-27	-27	-27	-27	-27	-28	-27	-27	-28
13	OE	(-) Other Operations Expenses	-36	-36	-36	-37	-37	-37	-37	-37	-38	-37	-37	-38
14	OE	(-) Insurance	-	-	-	-	-	-158	-	-	-	-	-	-
15	OE	(-) Property Tax	-	-	-137	-137	-	-	-137	-137	-	-137	-137	-
16		(-) Professional Services	-26	-26	-26	-27	-27	-27	-27	-27	-27	-27	-27	-27
17	OE	(-) O&M Fee	-63	-63	-63	-64	-65	-65	-65	-66	-66	-65	-66	-66
18	OE	(-) Management Fees	-69	-69	-69	-70	-70	-70	-70	-71	-71	-70	-71	-71
19			-19	-19	-19	-19	-19	-19	-19	-19	-20	-19	-19	-20

<tbody>
</tbody>

 

[Nishant94]

Do you want something like this:

Unknown
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		Month:	Jun
2		Result:	-3938
3
4
5			Jan-17	Feb-17	Mar-17	Apr-17	May-17	Jun-17	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17
6	OE	(-) Utilities	-127	-127	-127	-128	-129	-129	-129	-131	-132	-129	-131	-132
7		(-) Labor Costs	-328	-328	-328	-333	-335	-335	-335	-340	-342	-335	-340	-342
8	OE	(-) Plant Maintenance	-68	-68	-68	-69	-69	-69	-69	-70	-70	-69	-70	-70
9	OE	(-) Lease Engine Program	-333	-333	0	0	0	-340	0	0	0	0	0	0
10	OE	(-) Outside Services	-24	-24	-24	-24	-24	-24	-24	-25	-25	-24	-25	-25
11		(-) Plant Office & Admin	-27	-27	-27	-28	-28	-28	-28	-28	-28	-28	-28	-28
12	OE	(-) Chemicals	-26	-26	-26	-27	-27	-27	-27	-27	-28	-27	-27	-28
13	OE	(-) Other Operations Expenses	-36	-36	-36	-37	-37	-37	-37	-37	-38	-37	-37	-38
14	OE	(-) Insurance	0	0	0	0	0	-158	0	0	0	0	0	0
15	OE	(-) Property Tax	0	0	-137	-137	0	0	-137	-137	0	-137	-137	0
16		(-) Professional Services	-26	-26	-26	-27	-27	-27	-27	-27	-27	-27	-27	-27
17	OE	(-) O&M Fee	-63	-63	-63	-64	-65	-65	-65	-66	-66	-65	-66	-66
18	OE	(-) Management Fees	-69	-69	-69	-70	-70	-70	-70	-71	-71	-70	-71	-71
19			-19	-19	-19	-19	-19	-19	-19	-19	-20	-19	-19	-20
Sheet16

Cell Formulas
Range	Formula
I2	=SUMPRODUCT((G6:G19="OE")*(I6:INDEX(I19:T19,MATCH(DATEVALUE(1&I1&2017),I5:T5,0))))

 

[castilian07]

Wow, It's amazing, thank you very much. I would have never thought of the I6: semicolon. Index piece: I'm curious why it works? Thanks so much again. This will make life easier.

 

[castilian07]

Thanks NiMip, I am doing something wrong. I keep getting the 0 indicating there is an error. The match function works in the offset formula as it results 6 columns. I did not get the{} brackets arround the formula. I will keep trying.

 

[NiMip]

The solution from Nishant94 looks pretty suave anyway, so I wouldn't stress! But the issue would be the control+shift+enter. When you put in the formula, instead of just pressing enter you have to press control, shift and enter all together.