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.

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.

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.