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:


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

Generic formula



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


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


The ABS function changes any negative values to positive:


This array is compared to the fixed tolerance in F5:


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:


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


Highlight values out of tolerance

This page lists more examples of conditional formatting with formulas.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.