Countif and absolute values

[sscornav]

I have a column of percentages and want to count the number of cells with values <=10% and >=-10% (abs(10))

It seems like I should be able to do this with COUNTIF, but I am struggling. Any help is appreciated.

Thanks!

 

[MrKowz]

Try:

=SUMPRODUCT(--(ABS(A1:A100)<=0.10))

 

[T. Valko]

I have a column of percentages and want to count the number of cells with values <=10% and >=-10% (abs(10))

It seems like I should be able to do this with COUNTIF, but I am struggling. Any help is appreciated.

Thanks!

Try this...

=COUNTIF(A2:A10,">=-0.1")-COUNTIF(A2:A10,">0.1")

 

[sscornav]

Thanks! Both work! Brain freeze today. The second was so simple I should have thought of it. I have little experience with sumproduct.

 

[sscornav]

=SUMPRODUCT(--(ABS(A1:A100)<=0.10))

Actually, this give a VALUE error, can you explain what it's trying to do?
<!-- / message --><!-- sig -->

 

[MrKowz]

It shouldn't be giving you an error. Are there any errors in your data?

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">-0.82036</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">0.015365</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">-0.51259</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">-0.02548</td><td style="text-align: right;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet2</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">ABS(<font color="Green">A1:A6</font>)<=0.1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />



However, what it does is look at the absolute value of all of the values from A1:A100. Then if that value is less than or equal to 0.10, it adds 1 to the count.

 

[T. Valko]

=SUMPRODUCT(--(ABS(A1:A100)<=0.10))

Actually, this give a VALUE error, can you explain what it's trying to do?
<!-- / message --><!-- sig -->

If there is any non-numeric data (text?) in the range then ABS will generate the #VALUE! error.

 

[sscornav]

That was the issue, one row had a non-numeric value. What does the "--" do before the ABS? I appreciate your answers.

Thanks!

 

[T. Valko]

That was the issue, one row had a non-numeric value. What does the "--" do before the ABS? I appreciate your answers.

=SUMPRODUCT(--(ABS(A1:A6)<=0.1))

Thanks!

This portion of the formula will return an array of TRUE or FALSE:

ABS(A1:A6)<=0.1

That array might look something like this:

ABS(A1)<=0.1 = FALSE
ABS(A2)<=0.1 = TRUE
ABS(A3)<=0.1 = TRUE
ABS(A4)<=0.1 = TRUE
ABS(A5)<=0.1 = FALSE
ABS(A6)<=0.1 = TRUE

SUMPRODUCT works with numbers so we need some means of converting that array of logical values to numeric values. The double unary minus -- is one way to do that.

--TRUE = 1
--FALSE = 0

So:

--(ABS(A1)<=0.1) = 0
--(ABS(A2)<=0.1) = 1
--(ABS(A3)<=0.1) = 1
--(ABS(A4)<=0.1) = 1
--(ABS(A5)<=0.1) = 0
--(ABS(A6)<=0.1) = 1

Then SUMPRODUCT sums this array of numbers to return the count:

SUMPRODUCT({0;1;1;1;0;1})
=4

 

[sscornav]

Excellent explanation! Thank you.