Year to Date v Same Last Year

[alancyoung]

I hope someone can help me.
See table below.
[TABLE="width: 896"]
<colgroup><col width="64" span="14" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]Jan[/TD]
[TD="class: xl65, width: 64"]Feb[/TD]
[TD="class: xl65, width: 64"]Mar[/TD]
[TD="class: xl65, width: 64"]Apr[/TD]
[TD="class: xl65, width: 64"]May[/TD]
[TD="class: xl65, width: 64"]Jun[/TD]
[TD="class: xl65, width: 64"]Jul[/TD]
[TD="class: xl65, width: 64"]Aug[/TD]
[TD="class: xl65, width: 64"]Sep[/TD]
[TD="class: xl65, width: 64"]Oct[/TD]
[TD="class: xl65, width: 64"]Nov[/TD]
[TD="class: xl65, width: 64"]Dec[/TD]
[TD="class: xl65, width: 64"]YTD[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2011[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]700[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2012[/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65, align: right"]800

[/TD]
[/TR]
</tbody>[/TABLE]
I am looking for a formula which will SUM Row 2011 in the YTD Column to give a value of 700 as shown. This is because, up until June 2011 the total was 700, which allows me to compare to the 800 value in 2012.

When I input July, I want the value in YTD 2011 to update too, which in this case would remain as 700 due to the 0 value in 2011.

Hope this makes sense and you are able to help.

Thanks

Alan

 

[AhoyNC]

Here is one way that should work.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	YTD
2	2011	100	100	0	100	200	200	0	100	100	200	100	100	700
3	2012	200	200	100	0	100	200							800
Sheet

 

[alancyoung]

Thanks, but I have an added issue with this solution.
H3:M3 contains formula which at present, returns a zero value, but I don't want N2 to SUM B2:H2. I want it to SUM B2:G2.

However, if the actual figure in H3 is 0, then I want N2 to SUM B2:H2. No idea how to solve, but desperately need help.

Thanks

 

[T. Valko]

I hope someone can help me.
See table below.
[TABLE="width: 896"]
<TBODY>[TR]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"]Jan
[/TD]
[TD="class: xl65, width: 64"]Feb
[/TD]
[TD="class: xl65, width: 64"]Mar
[/TD]
[TD="class: xl65, width: 64"]Apr
[/TD]
[TD="class: xl65, width: 64"]May
[/TD]
[TD="class: xl65, width: 64"]Jun
[/TD]
[TD="class: xl65, width: 64"]Jul
[/TD]
[TD="class: xl65, width: 64"]Aug
[/TD]
[TD="class: xl65, width: 64"]Sep
[/TD]
[TD="class: xl65, width: 64"]Oct
[/TD]
[TD="class: xl65, width: 64"]Nov
[/TD]
[TD="class: xl65, width: 64"]Dec
[/TD]
[TD="class: xl65, width: 64"]YTD
[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2011
[/TD]
[TD="class: xl65, align: right"]100
[/TD]
[TD="class: xl65, align: right"]100
[/TD]
[TD="class: xl65, align: right"][/TD]
[TD="class: xl65, align: right"]100
[/TD]
[TD="class: xl65, align: right"]200
[/TD]
[TD="class: xl65, align: right"]200
[/TD]
[TD="class: xl65, align: right"][/TD]
[TD="class: xl65, align: right"]100
[/TD]
[TD="class: xl65, align: right"]100
[/TD]
[TD="class: xl65, align: right"]200
[/TD]
[TD="class: xl65, align: right"]100
[/TD]
[TD="class: xl65, align: right"]100
[/TD]
[TD="class: xl65, align: right"]700
[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2012
[/TD]
[TD="class: xl65, align: right"]200
[/TD]
[TD="class: xl65, align: right"]200
[/TD]
[TD="class: xl65, align: right"]100
[/TD]
[TD="class: xl65, align: right"][/TD]
[TD="class: xl65, align: right"]100
[/TD]
[TD="class: xl65, align: right"]200
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]800

[/TD]
[/TR]
</TBODY>[/TABLE]
I am looking for a formula which will SUM Row 2011 in the YTD Column to give a value of 700 as shown. This is because, up until June 2011 the total was 700, which allows me to compare to the 800 value in 2012.

When I input July, I want the value in YTD 2011 to update too, which in this case would remain as 700 due to the 0 value in 2011.

Hope this makes sense and you are able to help.

Thanks

Alan

Here are a couple of ways:

=SUM(B2:INDEX(B2:M2,MATCH("Jul",B1:M1,0)))

Or, if you want the YTD based on the month of today's date:

=SUM(B2:INDEX(B2:M2,MONTH(NOW())))

This one will automatically update when the month changes.

 

[alancyoung]

Thanks, but I'm afraid both can't be used.

Using the 1st formula only relates to July, so once I go beyond July it stops calculating, and I can't use the 2nd formula, as I may want to reference the document in another month without updating. I don't think there is an easy answer to this one!

 

[T. Valko]

Thanks, but I'm afraid both can't be used.

Using the 1st formula only relates to July, so once I go beyond July it stops calculating, and I can't use the 2nd formula, as I may want to reference the document in another month without updating. I don't think there is an easy answer to this one!

There is, we just need to understand what you want to do.

Ok, then use this version:

=SUM(B2:INDEX(B2:M2,MATCH(O1,B1:M1,0)))

Where O1 = Jul (or whatever month you want).

Just make sure you enter the month name as they appear in B1:M1.

 

[alancyoung]

Could you explain the formula above so I can follow it a bit better? From what I understand, I would need to re-enter the formula depending on what month I was referencing? Correct?

If I opened my spreadsheet in November 2012 for instance, would the figure in my worksheet relate to Jul?

 

[T. Valko]

Could you explain the formula above so I can follow it a bit better? From what I understand, I would need to re-enter the formula depending on what month I was referencing? Correct?

No.

You enter the ending month in cell O1 (or whatever cell you want to use).

So, if you enter Jul in cell O1 then the sum range will be from Jan to Jul. It sounded like you wanted some flexibility in being able to select the ending month. So, using an input cell to enter the ending month is the way to go (if that's what you wanted to do).

If I opened my spreadsheet in November 2012 for instance, would the figure in my worksheet relate to Jul?

Using this formula:

=SUM(B2:INDEX(B2:M2,MATCH(O1,B1:M1,0)))

It will sum fron Jan to whatever month is entered in cell O1.

The other formula I suggested:

=SUM(B2:INDEX(B2:M2,MONTH(NOW())))

Will automatically update based on the month of today's date.

Since today is July 17, the formula would sum from Jan to Jul. On August 1st, the formula will sum from Jan to Aug. On September 1st, the formula will sum from Jan to Sep.