AutoFiltered array formula- is it poss ?? 1

[happymc100]

Afternoon Everyone,
I hope someone can help me.
I have entered for someone the following formula in cell K2. It counts the number of times a selection appears in column K below the fomula. "xxx" is only sample data

{=SUM(IF(FREQUENCY(IF(K3:K1000="xxx",ROW(K3:K1000)),IF(K5:K1000="xxx",ROW(K5:K1000)))>0,1,0))}

Now, he wants to auto filter column K. This gives me the following problems.

1. Does an array formula work in a filtered list.
If so how ?

2. How do I change the txt constant "xxx" to ref the filter?
and not manually have to change it all the time.

Thanks in advance
Mike

 

[kpetursson]

use the subtotal function

=SUBTOTAL(3,K2:K13)

look it up in excel help to see all the options.

the 3 is the type function it applies to the filtered cells, 3 is count non-blank cells.

K2:K13 is the range in which you want to count.
I would suggest naming this range if in the future the range may grow. A named range will expand if you insert rows.



Kevin Petursson

I have a mind like a steel trap!
Nothing gets in,
Nothing gets out!
Just waiting for it to rust!

 

[happymc100]

Kevin,
Thanks for that, It really hits the mark. So simple really. Ive used the subtotal before for nr.s but not type.

Many thanks again
Mike