Sum the last 12 columns ?

[TroyB]

Hi,
I'm embarrased about asking this question!
I've got a table with a number of coulums (say 20) with $values in a number
of rows and i keep adding columns to the last one.What worksheet function do
i use to sum the last 12
columns in the table (so that i dont have to manually change the range each
time i add a column).

eg
Job Feb03 Mar03 April03 May03 June03 July03 Aug03
Sept03 Oct03 Nov03 Dec03 Jan04 Feb04 Mar04 April04
Total

JobA $87.00 $45.00 $42.00 $87.00 $45.00 $42.00 $87.00
$45.00 $42.00 $87.00 $45.00 $42.00 $87.00 $45.00 $42.00
=Add last 2Col (ie from May 03 to April 04)
Job B $87.00 $45.00 $42.00 $87.00 $45.00 $42.00 $87.00
$45.00 $42.00 $87.00 $45.00 $42.00 $87.00 $45.00 $42.00
=Add last 12Col
Job C $87.00 $45.00 $42.00 $87.00 $45.00 $42.00 $87.00
$45.00 $42.00 $87.00 $45.00 $42.00 $87.00 $45.00 $42.00
=Add last 12Col

Many Thanks

Troy

 

[Trevor Shuttleworth]

Troy

placed in cell M2, this will sum columns A through N

=SUM(OFFSET(M2,,-12,1,12))

You can add columns and it will adjust. Note that you will get a #REF!
error if there aren't 12 columns or more to the left of the formula. You
can compensate for that by checking for the error:

=IF(ISERROR(SUM(OFFSET(L2,,-12,1,12))),0,SUM(OFFSET(L2,,-12,1,12)))

Regards

Trevor

 

[Cecilkumara Fernando]

TroyB,
If you make the Total column as columnB and your months starts from columnC
then this will work
=SUM(OFFSET(C2,0,COUNT(C2:IV2)-1,1,-IF(COUNT(C2:IV2)>11,12,COUNT(C2:IV2))))

watch for the line warp in the mail above formula should be in one line
there must be at least one months data and you cant have empty cells in
between data
HTH
Cecil