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

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

**Excel:** `=COUNTIFS(B2:B11,">="&E1-E2,B2:B11,"<="&E1+E2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Part | Diameter (mm) |  | Target | 25 |
| P-01 | 25.02 |  | Tolerance | 0.05 |
| P-02 | 24.97 |  | In tolerance |  |
| P-03 | 25.06 |  |  |  |
| P-04 | 24.94 |  |  |  |
| P-05 | 25 |  |  |  |
| P-06 | 25.05 |  |  |  |
| P-07 | 24.95 |  |  |  |
| P-08 | 25.11 |  |  |  |
| P-09 | 24.99 |  |  |  |
| P-10 | 25.03 |  |  |  |

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

Result: 7

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

## Other ways

- `=SUMPRODUCT(--(ROUND(ABS(B2:B11-E1),6)<=E2))`: Measures 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.

## 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")`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-values-within-a-tolerance