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 nonblank, nonhidden cells.
Does that help?
***********
Regards,
Ron
XL2003, WinXP
"PeterM" wrote:
> 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!
