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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'd like to use a COUNTIF function with criteria being all visible rows set
in the column filter. Thanks-
 
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!
 
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.
 
So, my previous reply mixed in from a different question... der. But your
reply did help me count ocurrences on my filter.

Thanks!
 
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.
 
You must log in or register to reply here.

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

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

Back
Top