count the number of specific data after using autofilter

[kwfine]

Hi all,

I am using Excel 2002.
I got a hundred rows of data containing students' weights and heights.
Here are the sample data before applying autofilter:

A 175cm 50KG
B 175cm 51KG
C 180cm 50KG
D 175cm 50KG

I wanted to focus on the students with specific criteria, say students who got 50KG, so I applied the autoFilter, now the visible data after using autoFilter becomes:

A 175cm 50KG
C 180cm 50KG
D 175cm 50KG

But when I tried to count how many student who are 175cm tall after I have applied autoFilter(), the returned value from countif() was 3, not 2.
It is trivial that the value should be 2. It seemed that MS Excel has counted in student B who is 175cm 51KG
I have no idea how to solve this.
Could you help me please?

Thank you.

[Ron Coderre]

The SUBTOTAL function ignores rows hidden by filters.
=SUBTOTAL(3,A1:A10) counts the non-blank cells in that range.

Other first argument options.
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
Note: Adding 100 to any of those FuncNums causes the SUBTOTAL function to ignore HIDDEN rows, not just hidden FILTERED rows.
Example: =SUBTOTAL(103,A1:A20) counts non-blank, non-hidden cells.

Does that help?

[NBVC]

Have a look at the SUBTOTAL function.. it counts/sums, etc filtered lists.

e.g.

=SUBTOTAL(3,A1:A100) counts number of items in the filtered list in that range.

[JBeaucaire]

Your first list shows 3 people 175cm tall. You should correct your example data.

Use the SUBTOTAL function , the COUNTA option to count the number of visible items in your filtered range:

=SUBTOTAL(103, A2:A100)

[NBVC]

Does Excel 2002 support 103? I Don't remember when it became available....

[Ron Coderre]

I don't remember either...Since I've used every version from Excel 4 through Excel 2010, my recollections of the feature additions/deletions get a bit fuzzy sometimes.

[JBeaucaire]

Good catch, NBVC, the 100 series was added in Excel 2003 and all it does it suppress counting of HIDDEN rows, not filtered rows. Filtered rows are always suppressed, even when using the low-level functions.

Your version of the formula is perfect for that.