Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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- |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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- |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Improve Excel Help Text - Make easier to Find Function Refs | Excel Worksheet Functions | |||
How too set last edited date in excel and which user was it? | Excel Worksheet Functions | |||
how can I rank within a filtered list in Excel? | Excel Discussion (Misc queries) | |||
How do you list the numbers in a 3d reference in excel? | Excel Worksheet Functions | |||
Statistical Excel Function Question within Excel 2000... | Excel Worksheet Functions |