count with data filters on

[PeterM]

Is there a way in Excel 2003 to use one of the count functions on a sheet
that is using data filters. If I filter a column so that, for example, 10
rows out of a total of 100 rows are currently displayed, to use a counta or
countif function to count the rows filtered? In other words, I want the
count function to return 10, not 100...thanks in advance for your help!

 

[Ron Coderre]

Try using the SUBTOTAL function.
The value returned is dependent upon the first argument (parameter)
in the function.
Example:
=SUBTOTAL(9,A2:A100) returns the sum of visible numbers in a filtered range.

Here are other 1st arugument 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?
***********
Regards,
Ron

XL2003, WinXP

 

[Don Guillett]

Look in the help index for SUBTOTAL

 

[PeterM]

kewl....thank you very much

Try using the SUBTOTAL function.
The value returned is dependent upon the first argument (parameter)
in the function.
Example:
=SUBTOTAL(9,A2:A100) returns the sum of visible numbers in a filtered range.

Here are other 1st arugument 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?
***********
Regards,
Ron

XL2003, WinXP

 

[Gord Dibben]

See Help on the SUBTOTAL funtion.

=SUBTOTAL(2,range)

COUNT is 2 or 102 depending upon whether or not you also have hidden rows in
the range.


Gord Dibben MS Excel MVP

 

[weedfreer]

Now then Ron,

Is there an easy way (i.e. without the need to program a VBA new row button)
to make this formular autoupdate if i were to add a new row to the bottom of
the list?

 

[Ron Coderre]

If you're using Excel 2003...just make your data range a List

From the Excel Main Menu:
<data><List><Create List>

With the formula referencing the list data,
adding more rows to the list will cause the
formula reference to change to accommodate
the new data

Does that help?

Regards,

Ron Coderre
Microsoft MVP (Excel)

 

[weedfreer]

I am using Excel 2007...what you have said sounds logical but not sure how to
select that option in 2007. I do need to back save to older version for a
friend though