Specify columns to add for months MTD Diff

[Joe]

I have 12 columns for example 2007 (Jan-Dec) and then 12 more columns for
2008 (Jan-Dec) all lined up horizontally.

At the end of the 12 monthly 2008 columns I have a MTD Diff column.

I need a formula to check on the date or month I specify and ony calculate
up to the matching month. Using 2007 and 2008 as examples, the 2007 data is
all filled in all the way to Dec but I only want to add up Jan and Feb or
whatever the matching amount of months I am currently up to. Below are a
couple of examples. The 1st example shows Feb in G1 and a 2 in G4. This shows
that I want it to look at Feb and only give me a MTD Diff for 2 months (Jan
and Feb). Then as the second example shows, if I enter Mar in G1 I want it to
calculate with three months of columns and return a MTD Diff of 3 in G4. The
total for Jan and Feb 2007 is 4. The Total for Jan and Feb 2008 is 6, so the
diff is 2. In the second example the total for Jan-Feb-Mar 2007 is 6 and the
total for Jan-Feb-Mar 2008 is 9, so the diff is 3. Currently I have to edit
the formula's in the MTD Diff column to force it to only use the months I
want. I would like to automate this so I don't have to change it each month
manually.

A B C D E F G
1 Feb
2 2007 2007 2007 2008 2008 2008 MTD
3 Jan Feb Mar Jan Feb Mar DIFF
4 2 2 2 3 3 3 2


A B C D E F G
1 Mar
2 2007 2007 2007 2008 2008 2008 MTD
3 Jan Feb Mar Jan Feb Mar DIFF
4 2 2 2 3 3 3 3

Thank you.

 

[Roger Govier]

Hi Joe

Assuming 2007 Months are in A3:M3, and Month requested is in A1, the
cumulative figure is

=SUM($A4:INDEX($A$4:$M$4,MATCH($A$3:$M$3,$A$1,0)))

Clearly you would apply this to your 2008 range as well and subtract one
form the other to get your difference.
The above assumes that the Months are text values, not Excel date formatted
to show "mmm"

 

[Joe]

Roger,

I can't get it to work. Can you apply it to my simple example so I can see
what is going on. Or provide a little better explanation?

 

[Roger Govier]

My apologies Joe

in my haste I type the Match function the wrong way round.
It should have read
=SUM($A4:INDEX($A$4:$M$4,MATCH($A$1,$A$3:$M$3,0)))

Applying the formula to your exact example would be

=SUM($D$4:INDEX($D$4:$F$4,MATCH($A$1,$D$3:$F$3,0)))
-SUM($A$4:INDEX($A$4:$C$4,MATCH($A$1,$A$3:$C$3,0)))