Countif and absolute values

sscornav

Board Regular
Joined
Mar 20, 2010
Messages
125
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!
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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")
 
Upvote 0
Thanks! Both work! Brain freeze today. The second was so simple I should have thought of it. I have little experience with sumproduct.
 
Upvote 0
=SUMPRODUCT(--(ABS(A1:A100)<=0.10))

Actually, this give a VALUE error, can you explain what it's trying to do?
<!-- / message --><!-- sig -->
 
Upvote 0
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.
 
Upvote 0
=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.
 
Upvote 0
That was the issue, one row had a non-numeric value. What does the "--" do before the ABS? I appreciate your answers.

Thanks!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top