How do I reference an Excel filtered list in a function?

G

gordo

I'd like to use a COUNTIF function with criteria being all visible rows set
in the column filter. Thanks-
 
D

Domenic

Assuming that you'd like to count the number of cells in Column B that
meet the criteria, in a filtered list, try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B1
00="Criteria"))

If the criteria is a numerical value, remove the quotes.

Hope this helps!
 
A

Arvi Laanemets

Hi

SUBTOTAL is the function, which uses data filtered by autofilter as source.
The 1st parameter determines, which of 11 various functions is imited - look
for SUBTOTAL worksheet function in MS Excel Help.
 
G

gordo

So, my previous reply mixed in from a different question... der. But your
reply did help me count ocurrences on my filter.

Thanks!
 
G

gordo

Thanks Domenic,

I got this approach to work, but it wasn't easy :)
Your Excel sills are quite a bit beyond mine.

I ended up using the SUBTOTAL function to determine the number of results
returned after applying my autofilter.

Thanks again for your reply. You've help me understand SUMPRODUCT better.
 
Top