# Sum when filtered

• Hi everyone,

I am trying to have a running total at the top of a page that totals a lot of data, be able to filter the data by one of the columns and have the total change to only the filtered data.

I have include a small sample so you can see what I am trying to do.

Thank for any help to solve this problem.

Excelpower

## Files

• Re: Sum when filtered

Here you go.

use the subtotal function as follows:

=SUBTOTAL(9,G4:G28)

9 indicates a sum subtotal, if you do a help search on subtotal you will see all the options for instance 2 wuold give a subtotaled count.

regards
neil

## Files

• Re: Sum when filtered

Thanks neil for the response.

I will give the SUBTOTAL function try, I will also do a search on subtotal. Thanks for the tip, it will be very helpful.

Excelpower

• Re: Sum when filtered

Thanks neil,

The SUBTOTAL worked just like you stated.

Again, thanks for the help
Kent Achord

• Re: Sum when filtered

NeilUK66,

Very handy SUBTOTAL formula - thanks for that.

Curious if something similar can be done with one of the COUNT functions? We use filters excessively in a simple XL scheduling worksheet and frequently need to count the rows that are filtered by product, line, date etc. We currently go into Tools/Options/Calculation tab and select "Manual". This counts the # of rows filtered and displays the quantity in the bottom left corner. However, we have to switch it back to auto calculation for other formulae to work the way we want it to on the fly.

The # of rows varies from 20 to over 200 in a typical weekly schedule and all have a standard header. It would be handy to have the count (per filtered rows) displayed in one of the header rows. Thx.

• Re: Sum when filtered

Here, again, you would use SUBTOTAL...

For COUNT...

=SUBTOTAL(2,Range)

For COUNTA...

=SUBTOTAL(3,Range)

Hope this helps!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!