formula to give me year to date up to end of last month?

[tonywatsonhelp]

Hi Everyone,

Ok so I have a problem,

I need to calculate last year year to date figure.

I have the data set out in columns

[TABLE="width: 190"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]March 2018[/TD]
[TD],G[/TD]
[/TR]
[TR]
[TD="align: right"]April 2018[/TD]
[TD],J[/TD]
[/TR]
[TR]
[TD="align: right"]May 2018,[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD="align: right"]June 2018[/TD]
[TD],P[/TD]
[/TR]
[TR]
[TD="align: right"]July 2018[/TD]
[TD],S[/TD]
[/TR]
[TR]
[TD="align: right"]August 2018[/TD]
[TD],V[/TD]
[/TR]
[TR]
[TD="align: right"]September 2018,[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD="align: right"]October 2018[/TD]
[TD],AB[/TD]
[/TR]
[TR]
[TD="align: right"]November 2018[/TD]
[TD],AE[/TD]
[/TR]
[TR]
[TD="align: right"]December 2018,[/TD]
[TD]AH[/TD]
[/TR]
[TR]
[TD="align: right"]January 2019,[/TD]
[TD]AK[/TD]
[/TR]
[TR]
[TD="align: right"]February 2019,[/TD]
[TD]AN[/TD]
[/TR]
</tbody>[/TABLE]
Now what I need is a formula that can only add up the months we are past, automaticly changing when we go into a new month.

So If I was in October 2018 I would want from March to September so I need to add up G+J+M+P+S+V+Y and the row i'm on?

any ideas how to do this?

Thanks

Tony

 

[Scott T]

Assuming that you have excel dates in row 1 and is the first of the month and that the columns in between are blank or non numeric.

=SUM(G2:INDEX(G2:AN2,MATCH(EOMONTH(DATE(YEAR(TODAY()),MONTH(TODAY()),1),-2)+1,G1:AN1,0)))