PC Review


Reply
Thread Tools Rate Thread

count with data filters on

 
 
PeterM
Guest
Posts: n/a
 
      25th Aug 2008
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!
 
Reply With Quote
 
 
 
 
Ron Coderre
Guest
Posts: n/a
 
      25th Aug 2008
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!

 
Reply With Quote
 
 
 
 
Don Guillett
Guest
Posts: n/a
 
      25th Aug 2008
Look in the help index for SUBTOTAL

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
http://www.pcreview.co.uk/forums/(E-Mail Removed)
"PeterM" <(E-Mail Removed)> wrote in message
news:(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!


 
Reply With Quote
 
PeterM
Guest
Posts: n/a
 
      25th Aug 2008
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!

 
Reply With Quote
 
Gord Dibben
Guest
Posts: n/a
 
      25th Aug 2008
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!


 
Reply With Quote
 
weedfreer
Guest
Posts: n/a
 
      16th Sep 2008
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!!



 
Reply With Quote
 
Ron Coderre
Guest
Posts: n/a
 
      17th Sep 2008
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:(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!!
>
>
>

 
Reply With Quote
 
weedfreer
Guest
Posts: n/a
 
      17th Sep 2008
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:(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!!
> >
> >
> >

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Filters *SOS* Filters *SOS* Filters *SOS* =?Utf-8?B?dmFsZGFpd2F5?= Microsoft Access Queries 1 30th Aug 2007 03:48 AM
How to copy with filters but not copy the filters in the middle? =?Utf-8?B?enRhbG92ZQ==?= Microsoft Excel Misc 0 1st Nov 2006 05:53 PM
Filters, Subtotal & Intacted Results after the filters' Removal kasiopi Microsoft Excel Misc 5 24th Feb 2006 01:18 PM
Task filters should be linked to calendar filters =?Utf-8?B?ZmV0Y2g5OA==?= Microsoft Outlook Calendar 0 19th Jan 2006 09:06 PM
can I modify the list of filters in filters.txt to help reduce ju. =?Utf-8?B?REZpZWxkcw==?= Microsoft Outlook Discussion 1 5th Dec 2004 09:56 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 11:13 PM.