subtotal & countif

  • Thread starter Thread starter Soz
  • Start date Start date
S

Soz

I have two columns one with supervisor names and the other with technicians
monthly scores. I've autofiltered by supervisor and now have just his crew
and scores. I need to count the number of technicians shown but minus those
whose score equal zero. I tried =subtotal(2,a1:a50) and get the amount
of techs after autofiltering, but I still would like to minus those who have
a zero total.
 
Why don't you filter on the technicians, use custom and select not equal to
and put a zero there?
If you insists on a formula this will do it

=SUMPRODUCT(($A$2:$A$50<>0)*(SUBTOTAL(3,OFFSET($A$2,ROW($A$2:$A$50)-MIN(ROW(
$A$2:$A$50)),,))))

I assume you have a header in in A1

but as I said it is not needed in this case
 
Soz,

Does

=SUBTOTAL(2,A1:A50)-COUNTIF(A1:A50,0)

work for you?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top