View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Trevor Shuttleworth Trevor Shuttleworth is offline
external usenet poster
 
Posts: 1,089
Default Sum the last 12 columns ?

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


"TroyB" wrote in message
. au...
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