Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I limit the visible rows in Excel? | Excel Discussion (Misc queries) | |||
Hiding Rows if the linked rows are blank | Excel Discussion (Misc queries) | |||
color bands for visible rows only | Excel Discussion (Misc queries) | |||
flexible paste rows function that inserts the right number of rows | Excel Discussion (Misc queries) | |||
Copying Rows when hiding other rows | Excel Worksheet Functions |