Applying formulas to filtered data

[Guest]

Sorry if this is a basic question, but I can't find an answer within the help
menu. I have Office 2007.

I have a worksheet with 119 rows and multiple columns of data. I have
applied a filter to the first 4 columns (of the many - the rest of the colums
just contain raw data I want to filter) so I can filter data by certain
type/criteria.

I want to apply simple formulas at the bottom of my overall data based on
different filters. For Example, if I just want to sum up the entire column
of my data, I'll do =sum(F6:F124). But then I also want to come up with the
sum of the same column after I apply the filter. So, after I do the filter I
may only have 12 data points (rows) that match the filter criteria. Those
data do not come from sequential rows, but they come pretty randomly, so, if
I do the sum of those data, and select the remaining cells, it will be
=sum(F12:F83). The 12 data come from somewhere between rows 12 and 83, but
the formula is summing ALL rows between 12 and 83... even though when the
filter is applied and I select the cells for my SUM formula I only see 12
rows (row 12,13,14,15,32,33,54,55,80,81,82,83). Those are the only rows I
want it to SUM.

Is there a way to use these filters and also a formula to use to apply it
only to the cells that are applicable to the filter, but not the cells that
are filtered out inbetween? (I will also be doing similar simple formulas
using division of different SUMs from different columns, etc.)

Sorry for the long explanation, I was just trying to make my problem make
sense.

Thanks for the help.

 

[Pete_UK]

Instead of using SUM(, you should use SUBTOTAL(9, as in:

=SUBTOTAL(9,F6:F124)

This will give you the sum of the displayed values after filtering,
and will automatically adjust to a new filter being applied.

You might like to put these totals at the top of the sheet (insert a
new blank row above your filters) and use Window | Freeze Panes so
they are always visible, and it saves you from keep scrolling down to
the bottom to see the effect of applying a filter.

Hope this helps.

Pete

 

[Guest]

Pete,
Thank you very much for the help... I think it is on the right track for
what I need. But, like you mentioned, it will automatically adjust to a new
filter being applied.

What about when I apply a different filter and need to sum the displayed
values after the filter from the same column? One filter will display
certain values in column F and I'll need to show the SUM of those, but then
the next filter will display different values in column F and I'll need to
show the SUM of those too. Is there a way to have different Sums of filtered
values from the same column so it doesn't automatically update/change based
on a different filter?

Hope that question makes sense...

Thanks,
Keith

 

[Pete_UK]

I think I know what you are asking for, but SUBTOTAL won't do that for
you - you will need to record one subtotal in another cell (out of the
filter range, eg at the top) and then apply another filter.

Another approach would be to build up a list of unique values from
your filtered columns (perhaps in another sheet) and then you can
build up a table by using SUMIF or SUMPRODUCT formulae and get values
that way - if you want to follow this approach then you will need to
supply some further details like which columns you are interested in
and what values you have.

Hope this helps.

Pete

 

[Guest]

Pete,
I think SUMIF (or SUMIFS) is going to work for what I am trying to do. If I
come across a problem in the future I will try to address it or ask further
questions.

Thanks for the help.