Hi, I've been using SUBTOTAL(9, C3:C396) to sum a column. This works when I
filter the column - it sums only the rows that are displayed. However, it
doesn't allow me to easily add more rows as I have to keep changing the
range in each column that uses SUBTOTAL (there are 12). Is there a statement
that will allow me to sum the columns of only the number of rows that are
displayed after my list is filtered but does not use cell references. Thanks

Assuming the formula is (currently) in C397, then use

=SUBTOTAL(9, C3:OFFSET(C397,-1,0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"DavidS" wrote in message
...
Hi, I've been using SUBTOTAL(9, C3:C396) to sum a column. This works when
I
filter the column - it sums only the rows that are displayed. However, it
doesn't allow me to easily add more rows as I have to keep changing the
range in each column that uses SUBTOTAL (there are 12). Is there a
statement
that will allow me to sum the columns of only the number of rows that are
displayed after my list is filtered but does not use cell references.
Thanks

David,

I always put my subtotal formulae at the top of the worksheet, with the
filters applied on the row below, so that you can easily see the totals
whenever a filter is applied, without having to jump down to the bottom
of the data each time. With this approach, you can have a formula like:

=SUBTOTAL(9,C3:C65522)

perhaps in C1, so that you don't have to worry about adding extra data
in the future. I use 65522 as it is easy to remember - six,
double-five, double-two - and it is almost a complete column.

Hope this helps.

Pete

DavidS wrote:
Hi, I've been using SUBTOTAL(9, C3:C396) to sum a column. This works when I
filter the column - it sums only the rows that are displayed. However, it
doesn't allow me to easily add more rows as I have to keep changing the
range in each column that uses SUBTOTAL (there are 12). Is there a statement
that will allow me to sum the columns of only the number of rows that are
displayed after my list is filtered but does not use cell references. Thanks

You're welcome, David.

Pete

DavidS wrote:
Pete, Thanks very much for your help.

Pete_UK wrote:
David,

I always put my subtotal formulae at the top of the worksheet, with the
filters applied on the row below, so that you can easily see the totals
whenever a filter is applied, without having to jump down to the bottom
of the data each time. With this approach, you can have a formula like:

=SUBTOTAL(9,C3:C65522)

perhaps in C1, so that you don't have to worry about adding extra data
in the future. I use 65522 as it is easy to remember - six,
double-five, double-two - and it is almost a complete column.

Hope this helps.

Pete

DavidS wrote:
Hi, I've been using SUBTOTAL(9, C3:C396) to sum a column. This works when I
filter the column - it sums only the rows that are displayed. However, it
doesn't allow me to easily add more rows as I have to keep changing the
range in each column that uses SUBTOTAL (there are 12). Is there a statement
that will allow me to sum the columns of only the number of rows that are
displayed after my list is filtered but does not use cell references. Thanks