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!

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


"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!

Look in the help index for SUBTOTAL

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"PeterM" <(E-Mail Removed)> wrote in message
news:BE1BC1B0-489E-40B2-A83D-(E-Mail Removed)...
> 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!

kewl....thank you very much

"Ron Coderre" wrote:

> 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
>
>
> "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!

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

On Mon, 25 Aug 2008 13:20:02 -0700, PeterM
<(E-Mail Removed)> 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!

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?


--
Be nice to the geeks as one day you'll probably be working for one!!

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" <(E-Mail Removed)> wrote in message
news:34F5CF49-BB07-4B64-9C49-(E-Mail Removed)...
> 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?
>
>
> --
> Be nice to the geeks as one day you'll probably be working for one!!
>
>
>

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


--
Be nice to the geeks as one day you'll probably be working for one!!


"Ron Coderre" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:34F5CF49-BB07-4B64-9C49-(E-Mail Removed)...
> > 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?
> >
> >
> > --
> > Be nice to the geeks as one day you'll probably be working for one!!
> >
> >
> >