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

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

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

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!

In article ,
gordo wrote:

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

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

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.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


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

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

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


"Arvi Laanemets" wrote:

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.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


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

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

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

Thanks!


"Domenic" wrote:

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!

In article ,
gordo wrote:

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

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

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.



"Domenic" wrote:

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!

In article ,
gordo wrote:

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