Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum the last 12 columns ?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum the last 12 columns ?
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 "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Make Columns A&B on Sheet2 always equal Sheet1 table columns A&B | Excel Worksheet Functions | |||
putting 2 long columns into multiple columns in excel page and sor | Excel Discussion (Misc queries) | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
Pivot Table Creating New Columns that Subtract Two Existing Columns | Excel Discussion (Misc queries) |