count with filter

[lucas]

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 are: 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

 

[Domenic]

Have a look at the SUBTOTAL function in the help file...

=SUBTOTAL(3,Range)

Hope this helps!

 

[Ron Coderre]

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]

Thx! It helps!

Have a look at the SUBTOTAL function in the help file...

=SUBTOTAL(3,Range)

Hope this helps!

 

[lucas]

Hi Ron, yeah - helps! Problem solved. thx!

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