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

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

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

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

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