# Count Ifs value within a range

#### Hemibuk

##### New Member
Hi All,

I've searched similar posts but I could not find what I'm looking for (if it's out there shame on me). I have two columns of values and a range/tolerance +/-. I need to know if:

1) The values are in the tolerance
2) If they exceed the tolerance what is the value

I've been tackled this with the countif but with no success.

If you guys can help me out, it will be appreciated!
Thanks

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### WarPigl3t

##### Well-known Member
You should provide a dataset. I'm not too sure what you are asking. You have 2 columns with data. Column A and column B. A value in the A column is a number? A value in the B column is a number? If a value in a cell of the A column is not between the two tolerance amounts, then what is suppose to happen? What about if the value of the cell in the B column is outside of the two tolerance amounts, what is suppose to happen? What are the tolerance amounts? Are the tolerance amounts static or dynamic? Are the tolerance amounts located in a specific cell or are you going to hard code them into the formula? Where do you want the results to show up, column C?

#### Special-K99

##### Well-known Member
Can you provide some sample data

#### Hemibuk

##### New Member
Can you provide some sample data

Sure.

In column A I have the low end of the range, let's say 2.212

In column B I have the high end of the range, let's say 3.881

In column C I have the tolerance, let's say -/+ 1.5

In this case, the formula should help me to identify if the values provided in column A and B are within the range, and if not X number for which they are in defect or excess (under or above the range).

Thanks a lot

<colgroup><col span="2"></colgroup><tbody>
</tbody>

#### Special-K99

##### Well-known Member

=IF(ABS(B1-A1)<=C1,"Within range","outside range")

#### Special-K99

##### Well-known Member
...or possibly

=SUMPRODUCT(--(ABS(B1:B100-A1:A100)>=(C1:C100)))

#### Hemibuk

##### New Member
Thanks a bunch guys!

Replies
3
Views
74
Replies
1
Views
30
Replies
3
Views
81
Replies
7
Views
38
Replies
3
Views
68

1,109,351
Messages
5,528,192
Members
409,807
Latest member
nicky736

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...