Count Ifs value within a range

Hemibuk

New Member
Joined
Sep 25, 2014
Messages
3
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
Joined
May 25, 2014
Messages
1,609
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?

Too many unanswered questions. Please provide a dataset.
 

Hemibuk

New Member
Joined
Sep 25, 2014
Messages
3
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
Joined
Nov 7, 2006
Messages
8,425
Office Version
  1. 2019

ADVERTISEMENT

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

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,425
Office Version
  1. 2019
...or possibly

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,302
Messages
5,836,512
Members
430,436
Latest member
fefenouil

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
Top