Counting within a filtered range
 

Hi,

I have found out how to determine the number of items in a filtered range by
using the formula
=SUBTOTAL(2,A5:A3000). This works well and tells me the population "n" of
the data class being analysed.

I now need to find the total number of occurrences within the filtered set.

E.g. for finding how many times 5 appears in the filtered range

5
7
5
8
3
= 2 (i.e. 5 appears twice)

All attempts have yielded the count of occurrences of the unfiltered data or
a formula error.

Is it possible to achieve my objective? Any help greatfully appreciated,

regards

Jeff

Try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A5:A3000,ROW(A5:A300 0)-MIN(ROW(A5:A3000)),0
,1)),--(A5:A3000=5))

Hope this helps!

In article , "Jeff"
wrote:

Hi,

I have found out how to determine the number of items in a filtered range by
using the formula
=SUBTOTAL(2,A5:A3000). This works well and tells me the population "n" of
the data class being analysed.

I now need to find the total number of occurrences within the filtered set.

E.g. for finding how many times 5 appears in the filtered range

5
7
5
8
3
= 2 (i.e. 5 appears twice)

All attempts have yielded the count of occurrences of the unfiltered data or
a formula error.

Is it possible to achieve my objective? Any help greatfully appreciated,

regards

Jeff

This worked beautifully. Many thanks.


"Domenic" wrote in message
...
Try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A5:A3000,ROW(A5:A300 0)-MIN(ROW(A5:A3000)),0
,1)),--(A5:A3000=5))

Hope this helps!

In article , "Jeff"
wrote:

Hi,

I have found out how to determine the number of items in a filtered range
by
using the formula
=SUBTOTAL(2,A5:A3000). This works well and tells me the population "n"
of
the data class being analysed.

I now need to find the total number of occurrences within the filtered
set.

E.g. for finding how many times 5 appears in the filtered range

5
7
5
8
3
= 2 (i.e. 5 appears twice)

All attempts have yielded the count of occurrences of the unfiltered data
or
a formula error.

Is it possible to achieve my objective? Any help greatfully appreciated,

regards

Jeff