Filtering

[Tom]

Is there anyway to apply formulas to filtered data?

A simple example is that on a set of filtered data (where,
lets say, half of the results are omitted) a "COUNT"
function will still count those which have been filtered
out.

I know the contents of filtered data can be copied and
pasted elsewhere, but this seems like an additional step
which could be removed if formulas were applied to the
filtered data as opposed to the whole set!?

 

[Paul]

Is there anyway to apply formulas to filtered data?

A simple example is that on a set of filtered data (where,
lets say, half of the results are omitted) a "COUNT"
function will still count those which have been filtered
out.

I know the contents of filtered data can be copied and
pasted elsewhere, but this seems like an additional step
which could be removed if formulas were applied to the
filtered data as opposed to the whole set!?

Have a look at the SUBTOTAL function. This does more than you might suppose
(for example, count, max, min, average, etc.) and ignores any hidden rows
that result from filtering.

So, for example, if rows 2 to 10 are filtered such that only rows 4 and 7
remain visible,
=SUBTOTAL(2,A2:A10)
is equivalent to
=COUNT(A4,A7)

 

[Tom]

Paul wrote:
:: ::: Is there anyway to apply formulas to filtered data?
:::
::: A simple example is that on a set of filtered data (where,
::: lets say, half of the results are omitted) a "COUNT"
::: function will still count those which have been filtered
::: out.
:::
::: I know the contents of filtered data can be copied and
::: pasted elsewhere, but this seems like an additional step
::: which could be removed if formulas were applied to the
::: filtered data as opposed to the whole set!?
::
:: Have a look at the SUBTOTAL function. This does more than you might
:: suppose (for example, count, max, min, average, etc.) and ignores
:: any hidden rows that result from filtering.
::
:: So, for example, if rows 2 to 10 are filtered such that only rows 4
:: and 7 remain visible,
:: =SUBTOTAL(2,A2:A10)
:: is equivalent to
:: =COUNT(A4,A7)

Yeah know about those, but I tend to filter out a lot of data by many
different methods and use the custom filters too, which subtotal doesn't
support, nor does subtotal allow to filter by more than one field!

Ah well i'll just live with it!