Autofilter and FREQUENCY

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jan Kronsell - 06 Dec 2007 13:39 GMT

Hi!

I have this FREQUENCY formula in my srpeadsheet:

=FREQUENCY(P1:P40000;S1:S10)

If I autofilter the list, the formular will still display the results of all
rows. Is there away to get FREQUENCY only to count Filtered Rows? Or can the
same result be achieved any other way.

I thought of combining with the SUMPRODUCT() function, but as the arrays are
not identical in siz, this will not work.

Jan

Reply to this Message

Bob Phillips - 06 Dec 2007 14:14 GMT

jAN,

tRY THIS

=FREQUENCY(IF(SUBTOTAL(3,OFFSET(P1,ROW(INDIRECT("1:4000")),0)),P1:P4000),S1:S10)

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi!
>
[quoted text clipped - 10 lines]
>
> Jan

Reply to this Message

Bob Phillips - 06 Dec 2007 14:16 GMT

Sorry, correction

=FREQUENCY(IF(SUBTOTAL(3,OFFSET(P1,ROW(INDIRECT("1:40"))-1,0)),P1:P40),S1:S10)

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi!
>
[quoted text clipped - 10 lines]
>
> Jan

Reply to this Message

Jan Kronsell - 06 Dec 2007 22:34 GMT

Thank You. That did it :-)

Jan

> Sorry, correction
>
[quoted text clipped - 14 lines]
>>
>> Jan

Reply to this Message

Rate this thread: