Summation of a filtered column

[DavidS]

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

 

[Bob Phillips]

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)

 

[Pete_UK]

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

 

[Pete_UK]

You're welcome, David.

Pete

Pete, Thanks very much for your help.