Count values out of tolerance

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.

April 14, 2025 · 1 min · 49 words · Hannah Reed

Table of Contents

Explanation

This formula counts how many values are not in range of a fixed tolerance.

SUMPRODUCT then sums the items in the array, and returns a final result, 4.

A zero result will be evaluated as FALSE.

Highlight values out of tolerance

ABS converts negative numbers to positive numbers, and positive numbers are unaffected.

Highlight values out of tolerance

Highlight values out of tolerance

Highlight values out of tolerance

Highlight values out of tolerance

Excel formula: Value is between two numbers

Excel formula: Value is within tolerance

Excel SUMPRODUCT function

Excel ABS function

Highlight values out of tolerance