countif and subtotal

  • Thread starter Thread starter Wayne Burritt
  • Start date Start date
W

Wayne Burritt

Hi: I need to use countif -- or something similar -- on a column of data
that I can sort
by criteria. It's really pretty simple: I just want to count the numbers
in a column that are >= 0. Then I want to count the numbers in the same
column that are <0. Then I calc a ratio. I use the subtotal function for
the other calculations, but countif doesn't work with it. Any ideas?
Thanks, Wayne
 
The syntax for countif and > or < is countif(a1:a50,">0")

You can do the countifs you listed here to return 2 numbers, then divide
them, or just divide 1 countif by the other.
 
Or if you just want a text representation of the ratio, you can use something like:-

=COUNTIF(A1:A1000,">=0")&":"&COUNTIF(A1:A1000,"<0")
 
Thanks Ken -- the problem is I need to be able to use countif -- or
something similar -- and a sorted list. So, it needs to work like subtotal
does. Any ideas?

Wayne
 
Since the suggested CountIf formulas should work with "a sorted list", you
probably meant to say "a filtered list." Right?
 
Hi Aladin -- yes, you're right. I'm trying to use countif on a filtered
list.

Wayne
 
One way

=SUMPRODUCT(($A$2:$A$100>=0)*(SUBTOTAL(3,OFFSET($A$2,ROW($A$2:$A$100)-MIN(RO
W($A$2:$A$100)),,))))

where the total range of the filtered list is A2:A100, will count values >=0


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

will count negative values, adapt to fit your range
 
To sum negative values <0

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

just change the <0 to >=0 to sum positive values
 
Thanks! Works great!
Peo Sjoblom said:
To sum negative values <0

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

just change the <0 to >=0 to sum positive values

--

Regards,

Peo Sjoblom


=SUMPRODUCT(($A$2:$A$100>=0)*(SUBTOTAL(3,OFFSET($A$2,ROW($A$2:$A$100)-MIN(RO
=SUMPRODUCT(($A$2:$A$100<0)*(SUBTOTAL(3,OFFSET($A$2,ROW($A$2:$A$100)-MIN(ROW
Click to expand...
 
Back
Top