Autofilter and FREQUENCY

[Jan Kronsell]

Hi!

I have this FREQUENCY formula in my srpeadsheet:

=FREQUENCY(P140000;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

 

[Bob Phillips]

jAN,

tRY THIS

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

--
HTH

Bob

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

 

[Bob Phillips]

Sorry, correction

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

--
HTH

Bob

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

 

[Jan Kronsell]

Thank You. That did it

Jan

Sorry, correction

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

--
HTH

Bob

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