countif and subtotal

[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

 

[Dave R.]

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.

 

[Ken Wright]

Or if you just want a text representation of the ratio, you can use something like:-

=COUNTIF(A1:A1000,">=0")&":"&COUNTIF(A1:A1000,"<0")

 

[Wayne Burritt]

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

 

[Aladin Akyurek]

Since the suggested CountIf formulas should work with "a sorted list", you
probably meant to say "a filtered list." Right?

 

[Wayne Burritt]

Hi Aladin -- yes, you're right. I'm trying to use countif on a filtered
list.

Wayne

 

[Peo Sjoblom]

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

 

[Wayne Burritt]

Thanks Peo Sjoblom -- it works great. How can I adjust it to SUM the values

= to 0 and SUM the values <0?

Thanks, Wayne

 

[Peo Sjoblom]

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

 

[Wayne Burritt]

Thanks! Works great!

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