software

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 quot;sexquot;.

What I wanna do is to count the names I have after using filter. E.g. if I
choose quot;malequot; I will have 2 names shown so I want this count formula to show
quot;2quot;. But when I choose sex quot;femalequot; I'll have 3 names so wanna my formula to
result in quot;3quot;.

The problem is that I don't know how to set up this formula. Any idea?

Thx, lucas

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

=SUBTOTAL(3,Range)

Hope this helps!

In article gt;,
quot;lucasquot; gt; wrote:

gt; Hi All,
gt;
gt; I was trying to figure it out by myself but failed ;-(
gt;
gt; I have a column with names and let's say the names I have a Brad, George,
gt; Pamela, Cindy and Demi. I have also the second column that describes the sex
gt; of the name (male for Brad and George while female for the rest). Finally I
gt; have filter set up in the cell quot;sexquot;.
gt;
gt; What I wanna do is to count the names I have after using filter. E.g. if I
gt; choose quot;malequot; I will have 2 names shown so I want this count formula to show
gt; quot;2quot;. But when I choose sex quot;femalequot; I'll have 3 names so wanna my formula to
gt; result in quot;3quot;.
gt;
gt; The problem is that I don't know how to set up this formula. Any idea?
gt;
gt; Thx, lucas

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 NumFunction
1AVERAGE
2COUNT
3COUNTA
4MAX
5MIN
6PRODUCT
7STDEV
8STDEVP
9SUM
10VAR
11VARP

Without filtering, you could use =COUNTIF(select_your_col,quot;malequot;)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Proquot;lucasquot; wrote:

gt; Hi All,
gt;
gt; I was trying to figure it out by myself but failed ;-(
gt;
gt; I have a column with names and let's say the names I have a Brad, George,
gt; Pamela, Cindy and Demi. I have also the second column that describes the sex
gt; of the name (male for Brad and George while female for the rest). Finally I
gt; have filter set up in the cell quot;sexquot;.
gt;
gt; What I wanna do is to count the names I have after using filter. E.g. if I
gt; choose quot;malequot; I will have 2 names shown so I want this count formula to show
gt; quot;2quot;. But when I choose sex quot;femalequot; I'll have 3 names so wanna my formula to
gt; result in quot;3quot;.
gt;
gt; The problem is that I don't know how to set up this formula. Any idea?
gt;
gt; Thx, lucas

Thx! It helps!

quot;Domenicquot; wrote:

gt; Have a look at the SUBTOTAL function in the help file...
gt;
gt; =SUBTOTAL(3,Range)
gt;
gt; Hope this helps!
gt;
gt; In article gt;,
gt; quot;lucasquot; gt; wrote:
gt;
gt; gt; Hi All,
gt; gt;
gt; gt; I was trying to figure it out by myself but failed ;-(
gt; gt;
gt; gt; I have a column with names and let's say the names I have a Brad, George,
gt; gt; Pamela, Cindy and Demi. I have also the second column that describes the sex
gt; gt; of the name (male for Brad and George while female for the rest). Finally I
gt; gt; have filter set up in the cell quot;sexquot;.
gt; gt;
gt; gt; What I wanna do is to count the names I have after using filter. E.g. if I
gt; gt; choose quot;malequot; I will have 2 names shown so I want this count formula to show
gt; gt; quot;2quot;. But when I choose sex quot;femalequot; I'll have 3 names so wanna my formula to
gt; gt; result in quot;3quot;.
gt; gt;
gt; gt; The problem is that I don't know how to set up this formula. Any idea?
gt; gt;
gt; gt; Thx, lucas
gt;

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

quot;Ron Coderrequot; wrote:

gt; If you are using AutoFilter (or Advanced Filter) you can probably use the
gt; SUBTOTAL function with the function argument set to either the 2 or 3 (see
gt; the list below and check Excel Help).
gt;
gt; Example: =SUBTOTAL(3,select_your_col)
gt;
gt; Func NumFunction
gt; 1AVERAGE
gt; 2COUNT
gt; 3COUNTA
gt; 4MAX
gt; 5MIN
gt; 6PRODUCT
gt; 7STDEV
gt; 8STDEVP
gt; 9SUM
gt; 10VAR
gt; 11VARP
gt;
gt; Without filtering, you could use =COUNTIF(select_your_col,quot;malequot;)
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;lucasquot; wrote:
gt;
gt; gt; Hi All,
gt; gt;
gt; gt; I was trying to figure it out by myself but failed ;-(
gt; gt;
gt; gt; I have a column with names and let's say the names I have a Brad, George,
gt; gt; Pamela, Cindy and Demi. I have also the second column that describes the sex
gt; gt; of the name (male for Brad and George while female for the rest). Finally I
gt; gt; have filter set up in the cell quot;sexquot;.
gt; gt;
gt; gt; What I wanna do is to count the names I have after using filter. E.g. if I
gt; gt; choose quot;malequot; I will have 2 names shown so I want this count formula to show
gt; gt; quot;2quot;. But when I choose sex quot;femalequot; I'll have 3 names so wanna my formula to
gt; gt; result in quot;3quot;.
gt; gt;
gt; gt; The problem is that I don't know how to set up this formula. Any idea?
gt; gt;
gt; gt; Thx, lucas