Spreadsheet Practice

How to count values within a tolerance of a target in Excel

Formula (Excel and Google Sheets)

=COUNTIFS(B2:B11,">="&E1-E2,B2:B11,"<="&E1+E2)

Count between the target minus and plus the tolerance: =COUNTIFS(B2:B11,">="&E1-E2,B2:B11,"<="&E1+E2).

Result on the example
7 in E3
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in E3. Change any input and the result updates, or edit the formula to experiment.

How it works

"Within 0.05 of 25" is the range 24.95 to 25.05. The formula builds both bounds from the target and tolerance cells, and COUNTIFS counts the diameters between them, bounds included. Seven parts pass, including the two exactly on the limits.

=COUNTIFS(B2:B11,">="&E1-E2,B2:B11,"<="&E1+E2)

Result7

  1. B2:B1110 rows × 1 col: 25.02, 24.97, 25.06, 24.94, …

    A range: 10 cells in one column.

  2. E125.00

    The value in E1.

  3. E20.05

    The value in E2.

  4. E1-E224.95

    The - operator subtracts.

  5. ">="&E1-E2">=24.95"

    The & operator joins as text.

  6. E1+E225.05

    The + operator adds.

  7. "<="&E1+E2"<=25.05"

    The & operator joins as text.

  8. COUNTIFS(B2:B11,">="&E1-E2,B2:B11,"<="&E1+E2)7

    COUNTIFS: Counts rows that meet every condition.

    • criteria_range1B2:B1110 rows × 1 col: 25.02, 24.97, 25.06, 24.94, …
    • criteria1">="&E1-E2">=24.95"
    • criteria_range2B2:B1110 rows × 1 col: 25.02, 24.97, 25.06, 24.94, …
    • criteria2"<="&E1+E2"<=25.05"

In Google Sheets

The same formula works in Google Sheets: =COUNTIFS(B2:B11,">="&E1-E2,B2:B11,"<="&E1+E2). We ran it in Google Sheets mode on the same data and it returns 7.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=SUMPRODUCT(--(ROUND(ABS(B2:B11-E1),6)<=E2))7Measures each part's distance from the target. ROUND clears the tiny floating-point error in the subtraction.

Common mistakes

  • Spreadsheets store decimals in binary, so 25.05-25 is stored as 0.0500000000000007, slightly more than 0.05. A test on the difference can then reject a part exactly on the limit; ROUND the difference, as in the alternative, to be safe.
  • Keep the tolerance in its own cell; for a percentage tolerance use E1*(1-E2) and E1*(1+E2) as the bounds.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Count values within a tolerance of a target

In E3, count the parts whose diameter is within the tolerance in E2 of the target in E1.

Questions

How do I count parts outside the tolerance?

Subtract from the total: =COUNT(B2:B11)-E3.

How do I flag each part as pass or fail?

In a helper column: =IF(ROUND(ABS(B2-$E$1),6)<=$E$2,"Pass","Fail").

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.