Summation of a filtered column

  • Thread starter Thread starter DavidS
  • Start date Start date
D

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
 
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)
 
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
 
Back
Top