Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count with filter
Hi All,
I was trying to figure it out by myself but failed ;-( I have a column with names and let's say the names I have a Brad, George, Pamela, Cindy and Demi. I have also the second column that describes the sex of the name (male for Brad and George while female for the rest). Finally I have filter set up in the cell "sex". What I wanna do is to count the names I have after using filter. E.g. if I choose "male" I will have 2 names shown so I want this count formula to show "2". But when I choose sex "female" I'll have 3 names so wanna my formula to result in "3". The problem is that I don't know how to set up this formula. Any idea? Thx, lucas |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count with filter
Have a look at the SUBTOTAL function in the help file...
=SUBTOTAL(3,Range) Hope this helps! In article , "lucas" wrote: Hi All, I was trying to figure it out by myself but failed ;-( I have a column with names and let's say the names I have a Brad, George, Pamela, Cindy and Demi. I have also the second column that describes the sex of the name (male for Brad and George while female for the rest). Finally I have filter set up in the cell "sex". What I wanna do is to count the names I have after using filter. E.g. if I choose "male" I will have 2 names shown so I want this count formula to show "2". But when I choose sex "female" I'll have 3 names so wanna my formula to result in "3". The problem is that I don't know how to set up this formula. Any idea? Thx, lucas |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count with filter
If you are using AutoFilter (or Advanced Filter) you can probably use the
SUBTOTAL function with the function argument set to either the 2 or 3 (see the list below and check Excel Help). Example: =SUBTOTAL(3,select_your_col) Func Num Function 1 AVERAGE 2 COUNT 3 COUNTA 4 MAX 5 MIN 6 PRODUCT 7 STDEV 8 STDEVP 9 SUM 10 VAR 11 VARP Without filtering, you could use =COUNTIF(select_your_col,"male") Does that help? *********** Regards, Ron XL2002, WinXP-Pro "lucas" wrote: Hi All, I was trying to figure it out by myself but failed ;-( I have a column with names and let's say the names I have a Brad, George, Pamela, Cindy and Demi. I have also the second column that describes the sex of the name (male for Brad and George while female for the rest). Finally I have filter set up in the cell "sex". What I wanna do is to count the names I have after using filter. E.g. if I choose "male" I will have 2 names shown so I want this count formula to show "2". But when I choose sex "female" I'll have 3 names so wanna my formula to result in "3". The problem is that I don't know how to set up this formula. Any idea? Thx, lucas |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count with filter
Thx! It helps!
"Domenic" wrote: Have a look at the SUBTOTAL function in the help file... =SUBTOTAL(3,Range) Hope this helps! In article , "lucas" wrote: Hi All, I was trying to figure it out by myself but failed ;-( I have a column with names and let's say the names I have a Brad, George, Pamela, Cindy and Demi. I have also the second column that describes the sex of the name (male for Brad and George while female for the rest). Finally I have filter set up in the cell "sex". What I wanna do is to count the names I have after using filter. E.g. if I choose "male" I will have 2 names shown so I want this count formula to show "2". But when I choose sex "female" I'll have 3 names so wanna my formula to result in "3". The problem is that I don't know how to set up this formula. Any idea? Thx, lucas |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count with filter
Hi Ron, yeah - helps! Problem solved. thx!
"Ron Coderre" wrote: If you are using AutoFilter (or Advanced Filter) you can probably use the SUBTOTAL function with the function argument set to either the 2 or 3 (see the list below and check Excel Help). Example: =SUBTOTAL(3,select_your_col) Func Num Function 1 AVERAGE 2 COUNT 3 COUNTA 4 MAX 5 MIN 6 PRODUCT 7 STDEV 8 STDEVP 9 SUM 10 VAR 11 VARP Without filtering, you could use =COUNTIF(select_your_col,"male") Does that help? *********** Regards, Ron XL2002, WinXP-Pro "lucas" wrote: Hi All, I was trying to figure it out by myself but failed ;-( I have a column with names and let's say the names I have a Brad, George, Pamela, Cindy and Demi. I have also the second column that describes the sex of the name (male for Brad and George while female for the rest). Finally I have filter set up in the cell "sex". What I wanna do is to count the names I have after using filter. E.g. if I choose "male" I will have 2 names shown so I want this count formula to show "2". But when I choose sex "female" I'll have 3 names so wanna my formula to result in "3". The problem is that I don't know how to set up this formula. Any idea? Thx, lucas |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Filter | Excel Discussion (Misc queries) | |||
count records which meet filter criteria | Excel Discussion (Misc queries) | |||
Filter Count of Records Retrieved. | Excel Discussion (Misc queries) | |||
filter and count | Excel Discussion (Misc queries) | |||
How do I get a count when I filter an excel worksheet? | Excel Worksheet Functions |