Counting Frequency of Filtered Data

tom · 03-01-2006, 03:10 PM

I have a table of filtered data. For example

a1: 15
a2: 18
a10: 15

cells a3:a9 also have valid numbers but are filtered out.

Is there a way to count the frequency of only the filtered data.

For example in the above, value 15 has a frequency of 2, 18 only one.

Countif calculates the frequency of all rows, not just the visible ones.
Subtotal with option 2 counts the number of visible rows, in this case 3.

Thanks

Domenic · 03-01-2006, 04:35 PM

Try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-ROW(A2),0,1)),--(A2:A10=
15))

Hope this helps!

In article <B662F16E-D140-4C12-9246-E6FBD1A63F3E@microsoft.com>,
tom <tom@discussions.microsoft.com> wrote:

> I have a table of filtered data. For example
>
> a1: 15
> a2: 18
> a10: 15
>
> cells a3:a9 also have valid numbers but are filtered out.
>
> Is there a way to count the frequency of only the filtered data.
>
> For example in the above, value 15 has a frequency of 2, 18 only one.
>
> Countif calculates the frequency of all rows, not just the visible ones.
> Subtotal with option 2 counts the number of visible rows, in this case 3.
>
> Thanks

**Sam CHAU** · 10-17-2014, 07:40 AM

=MAX(FREQUENCY(IF(UJZ14:UJZ2000=UJZ13,ROW(UJZ14:UJZ2000)),IF(UJZ14:UJZ2000=UJZ13,,ROW(UJZ14:UJZ2000))))
Anyone can give a help to input the Subtotal inside this equation ? As I would like to know the total numbers of data after autofile.

**protonLeah** · 10-17-2014, 09:25 PM

Sam CHAU,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.