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

G

Guest

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

Guest

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

Thanks!
 
G

Guest

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Mode Function combined with a filter 1
COUNTIF___Visable only 4
How to make "countif" on a filtered excel-list? i.e. like subtotal 4
VBA: How can I use a string variable as autofilter criteria? 4
countifs Function not giving desired result with date criteria 2
Using Countif with autofillter 4
How to add only the adding filtered result using SUM function? 7
COUNTIF & SUBTOTAL 2

Top