Spreadsheet Practice

How to calculate standard deviation with criteria in Excel

Formula (Excel and Google Sheets)

=STDEV.S(FILTER(C2:C9,B2:B9=F1))

Filter the values, then take the standard deviation: =STDEV.S(FILTER(C2:C9,B2:B9=F1)).

Result on the example
3.17 in F2
Works in
Excel 2021, 2024 and Microsoft 365
Google Sheets
Same formula

See it work

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

How it works

There is no STDEVIFS. FILTER keeps the four M2 weights and STDEV.S measures their spread: about 3.17 g, much wider than machine M1, which points to the machine that needs adjusting.

=STDEV.S(FILTER(C2:C9,B2:B9=F1))

Result3.17122479

  1. C2:C98 rows × 1 col: 501.2, 498.4, 500.6, 503.9, …

    A range: 8 cells in one column.

  2. B2:B98 rows × 1 col: "M1", "M2", "M1", "M2", …

    A range: 8 cells in one column.

  3. F1"M2"

    The value in F1.

  4. B2:B9=F1{8 rows × 1 col} FALSE, TRUE, FALSE, TRUE, …

    The = operator tests whether the two sides are equal.

  5. FILTER(C2:C9,B2:B9=F1){4 rows × 1 col} 498.4, 503.9, 496.8, 501.5

    FILTER: Rows of a range that meet every condition. #N/A when none match.

    • rangeC2:C98 rows × 1 col: 501.2, 498.4, 500.6, 503.9, …
    • condition1B2:B9=F1{8 rows × 1 col} FALSE, TRUE, FALSE, TRUE, …
  6. STDEV.S(FILTER(C2:C9,B2:B9=F1))3.17122479

    STDEV: Sample standard deviation (same as STDEV.S).

    • number1FILTER(C2:C9,B2:B9=F1){4 rows × 1 col} 498.4, 503.9, 496.8, 501.5

In Google Sheets

The same formula works in Google Sheets: =STDEV.S(FILTER(C2:C9,B2:B9=F1)). We ran it in Google Sheets mode on the same data and it returns 3.17.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=STDEV.S(IF(B2:B9=F1,C2:C9))Excel only3.17For Excel 2019 and older, entered with Ctrl+Shift+Enter. IF returns FALSE for other machines, which STDEV.S ignores.

Common mistakes

  • If only one row matches, STDEV.S returns #DIV/0!.
  • In the IF version, a third argument of 0 adds zeros for the other machines and wrecks the result.

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.

Calculate standard deviation with criteria

In F2, calculate the standard deviation of the fill weights for the machine in F1 only.

Questions

How do I use two conditions?

Multiply the tests: =STDEV.S(FILTER(C2:C9,(B2:B9=F1)*(C2:C9>490))).

Does this work in Google Sheets?

Yes. Google Sheets has FILTER and STDEV.S.

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.