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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gordo - 17 Aug 2006 00:25 GMT

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

Reply to this Message

Domenic - 17 Aug 2006 00:56 GMT

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!

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

Reply to this Message

gordo - 17 Aug 2006 20:35 GMT

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

Reply to this Message

gordo - 17 Aug 2006 20:36 GMT

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.

Reply to this Message

Arvi Laanemets - 17 Aug 2006 07:13 GMT

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.

Signature

Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

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

Reply to this Message

gordo - 17 Aug 2006 20:35 GMT

Thanks Arvi!
SUBTOTAL(3,rage) solved my problem.

> Hi
>
[quoted text clipped - 5 lines]
> > set
> > in the column filter. Thanks-

Reply to this Message