# If All Cells Between Two Tolerances Pass...

#### innoin

##### New Member
Hello! I tried posting this question in another forum, but no one has been able to figure it out. It seemed like the easiest question I've ever asked, but it's stumping people more than any complex thing I've asked about.

I need to create a formula that scans cells A2:S2 and gives a "Pass"/"Fail" condition based on whether all the cells are between an upper and lower tolerance on a tolerance tab. If each individual cell is between tolerances (lets say 90-105), then "Pass", otherwise, "Fail". Sometimes only 2 or 3 of the cells in row 2 will contain a number. Sometimes all 19 cells will have data. When a cell is not used, NA() is used to cause an error, so the formula needs to know to ignore the error cells and only count cells with a value (although I can change the NA() to a word or just a blank if needed). I've tried 20 different If statements and 30 different Countifs...none work! I can get them to read only one side of the tolerance. It seems like this would be a common problem, but so far I haven't had luck finding any info on how to solve this.

This formula is in cell A2 and then I autofilled to S2: IF(Data!D24=0,NA(),COUNTIF(Data!D6:D502,">="&Tol!\$B\$3)-COUNTIF(Data!D6:D502,">"&Tol!\$C\$3))

I have a tolerance tab setup with the lower tolerance in cell B4 and an upper tolerance (which sometimes does not exist so I type "NA" into the cell) in cell C4.

I am trying to input the Pass/Fail statement in cell V2. I have tried a ton of formulas and am currently messing with this one that someone recommended on another forum to no avail: IF(SUM(COUNTIF(A2:S2,NA()),COUNTIF(A2:S2,"<="&Tol!B4),COUNTIF(A2:S2,">="&Tol!C4))=19,"Pass","Fail")

I've attached a quick snapshot of a sample sheet I built that's similar as an example (not my actual workbook since it contains sensitive data). It shows a tolerance of 90-NA since there's no upper tolerance, but I can't get it to work even if I enter an upper tolerance.

Thanks for the help or just taking a stab at it!

#### Attachments

• sample.PNG
8.1 KB · Views: 9

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### KRice

##### Well-known Member
Try this:
mrexcel_20200416.xlsm
ABCDEFGHIJKLMNOPQRSTUVW
1Time:Fail
2#N/A#N/A3184125#N/A101393939#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A
390-NA90-NA90-NA90-NA90-NA90-NA90-NA90-NA90-NA90-NA90-NA90-NA90-NA90-NA90-NA90-NA90-NA90-NA90-NA
4Tol:90105
Sheet20
Cell Formulas
RangeFormula
V1V1=IF(OR(AGGREGATE(15,6,\$A\$2:\$S\$2,1)<\$V\$4,AGGREGATE(14,6,\$A\$2:\$S\$2,1)>\$W\$4),"Fail","Pass")
K2:S2,F2,A2:B2A2=NA()

#### innoin

##### New Member
Ah-ha, thank you! That worked!

Replies
3
Views
124
Replies
3
Views
209
Replies
6
Views
139
Replies
3
Views
95
Replies
6
Views
424

1,127,594
Messages
5,625,693
Members
416,127
Latest member
MALEPINZON

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