Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dr Happy
 
Posts: n/a
Default Using COUNTIF with visible rows only

I am trying to analyse visible only rows. I have columns called NAME (sorted
alphabetically), GROUP (e.g. S1 through S7) and RESULT (numeric 1 to 5). I
need to count how many 4's there are in the RESULT column, and I want to
auto-filter so I only display group S1 or S2 etc but the count has to reflect
only those rows which are visible. The SUBTOTAL function doesn't seem to
allow for this. I somehow need to mix SUBTOTAL with COUNTIF. Help.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Using COUNTIF with visible rows only

=SUMPRODUCT((SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$20 )-ROW($B$1),,1)))*(C2:C20*
=4))

where B2:B20 is Group and C2:c20 is Result


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Dr Happy" wrote in message
...
I am trying to analyse visible only rows. I have columns called NAME

(sorted
alphabetically), GROUP (e.g. S1 through S7) and RESULT (numeric 1 to 5). I
need to count how many 4's there are in the RESULT column, and I want to
auto-filter so I only display group S1 or S2 etc but the count has to

reflect
only those rows which are visible. The SUBTOTAL function doesn't seem to
allow for this. I somehow need to mix SUBTOTAL with COUNTIF. Help.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dr Happy
 
Posts: n/a
Default Using COUNTIF with visible rows only

Thank you Bob. That's done the trick.
I just need to get my brain around what it's doing to make it do what it
does, as it were...

"Bob Phillips" wrote:

=SUMPRODUCT((SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$20 )-ROW($B$1),,1)))*(C2:C20Â*
=4))

where B2:B20 is Group and C2:c20 is Result


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Dr Happy" wrote in message
...
I am trying to analyse visible only rows. I have columns called NAME

(sorted
alphabetically), GROUP (e.g. S1 through S7) and RESULT (numeric 1 to 5). I
need to count how many 4's there are in the RESULT column, and I want to
auto-filter so I only display group S1 or S2 etc but the count has to

reflect
only those rows which are visible. The SUBTOTAL function doesn't seem to
allow for this. I somehow need to mix SUBTOTAL with COUNTIF. Help.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I limit the visible rows in Excel? tomcheesewright Excel Discussion (Misc queries) 3 May 19th 05 03:31 PM
Hiding Rows if the linked rows are blank KG Excel Discussion (Misc queries) 9 May 18th 05 02:32 AM
color bands for visible rows only tjtjjtjt Excel Discussion (Misc queries) 1 April 30th 05 08:16 PM
flexible paste rows function that inserts the right number of rows marika1981 Excel Discussion (Misc queries) 1 February 18th 05 03:40 AM
Copying Rows when hiding other rows Neutron1871 Excel Worksheet Functions 2 November 4th 04 12:38 AM


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

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"