Adding a SUM total to the last row in Active Form

[allico920]

I want to add a sum row to the bottom of an active form. For example, I have months across the columns and accounts down the rows. I want to perform a simple sum of all the accounts per month.

Where can I store this formula so that it holds? As of now when i rebuild the sheet the sum formulas changes to just hold the top row value.
The sum holds all rows when it is at the top of the account list but is there a way to keep it at the bottom?

Thanks!

[tomok]

I want to add a sum row to the bottom of an active form. For example, I have months across the columns and accounts down the rows. I want to perform a simple sum of all the accounts per month.

Where can I store this formula so that it holds? As of now when i rebuild the sheet the sum formulas changes to just hold the top row value.
The sum holds all rows when it is at the top of the account list but is there a way to keep it at the bottom?

Thanks!

Have your SUM formula add up all the current rows in the active form and one more row below. When the active form rebuilds it will then include all the new rows PLUS your blank row. Just make the height of the row 0 if you don't want to see it.

[allico920]

THANK YOU!!!!

[gtonkin]

Tomok's method is probably the most frequently used and preferred method.
In some case however you may need to do something other than sum with the range and adding the extra blank row may yield incorrect results e.g. averaging.

To get around this, you could reference the range and work with the column e.g.

Code: Select all

=AVERAGE(OFFSET(TM1RPTDATARNG1,0,COLUMN(),ROWS(TM1RPTDATARNG1),1))

IF you use the fx icon on the formula bar, the offset formula should be fairly self-explanatory.

This alternative may be of use to some who need to access a column in the AF for any number of reasons.