# Countif and absolute values

#### sscornav

##### Board Regular
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

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### MrKowz

##### Well-known Member
Try:

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

#### T. Valko

##### Well-known Member
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

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

#### sscornav

##### Board Regular
=SUMPRODUCT(--(ABS(A1:A100)<=0.10))

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

#### MrKowz

##### Well-known Member
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

##### Well-known Member
=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

##### Board Regular
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

##### Well-known Member
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

##### Board Regular
Excellent explanation! Thank you.

Replies
9
Views
190
Replies
3
Views
318
Replies
4
Views
130
Replies
14
Views
386
Replies
9
Views
230

1,191,587
Messages
5,987,515
Members
440,099
Latest member
wai2kit

### 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.

### Which adblocker are you using?

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

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