## Summary

To count values that are out of tolerance in a set of data, you can use a formula based on the SUMPRODUCT and ABS functions. In the example shown, the formula in F6 is:

``````=SUMPRODUCT(--(ABS(data-target)>tolerance))
``````

where "data" is the named range B5:B14, "target" is the named range F4, and "tolerance" is the named range F5.

## Generic formula

``=SUMPRODUCT(--(ABS(data-target)>tolerance))``

## Explanation

This formula counts how many values are not in range of a fixed tolerance. The variation of each value is calculated with this:

``````ABS(data-target)
``````

Because the named range "data" contains 10 values, subtracting the target value in F4 will created an array with 10 results:

``````{0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006}
``````

The ABS function changes any negative values to positive:

``````{0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006}
``````

This array is compared to the fixed tolerance in F5:

``````ABS(data-target)>tolerance
``````

The result is an array or TRUE FALSE values, and the double negative changes these to ones and zeros. Inside SUMPRODUCT, the final array looks like this:

``````{0;0;1;0;1;0;0;1;0;1}
``````

where zeros represent values within tolerance, and 1s represent values out of tolerance. SUMPRODUCT then sums the items in the array, and returns a final result, 4.

### All values within tolerance

To return "Yes" if all values in a data range are within a given tolerance, and "No" if not, you can adapt the formula like this:

``````=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")
``````

If SUMPRODUCT returns any number greater than zero, IF will evaluate the logical test as TRUE. A zero result will be evaluated as FALSE.

### Highlight values out of tolerance

You can highlight values out of tolerance with a conditional formatting rule based on a formula like this:

``````=ABS(B5-target)>tolerance
``````