How to / Sums and averages

How to calculate a median with a condition in Excel

Excel has no MEDIANIF. In Excel 2021 and Microsoft 365, use =MEDIAN(FILTER(B2:B6, A2:A6="Red")).

The median score of one team

ABCD
1TeamScore
2Red1030
3Blue40
4Red30
5Red50
6Blue20

In D2: =MEDIAN(FILTER(B2:B6, A2:A6="Red")) → 30

The Red scores are 10, 30 and 50.

How it works

  1. FILTER keeps the scores on the rows where the team is Red.
  2. MEDIAN finds the middle value of what is left.
  3. Unlike an average, the median is not pulled up or down by one unusual score.

Variations

  1. In older versions of Excel

    ABCD
    1TeamScore
    2Red1030
    3Blue40
    4Red30
    5Red50
    6Blue20

    In D2: =MEDIAN(IF(A2:A6="Red", B2:B6)) → 30

    In Excel 2019 and earlier, finish it with Ctrl+Shift+Enter.

  2. Two conditions

    ABCD
    1TeamScore
    2Red1040
    3Blue40
    4Red30
    5Red50
    6Blue20

    In D2: =MEDIAN(FILTER(B2:B6, (A2:A6="Red")*(B2:B6>=20))) → 40

    Multiply the tests: both must be true.

  3. The average with a condition

    ABCD
    1TeamScore
    2Red1030
    3Blue40
    4Red30
    5Red50
    6Blue20

    In D2: =AVERAGEIF(A2:A6, "Red", B2:B6) → 30

  4. The highest with a condition

    ABCD
    1TeamScore
    2Red1050
    3Blue40
    4Red30
    5Red50
    6Blue20

    In D2: =MAXIFS(B2:B6, A2:A6, "Red") → 50

Watch out for

Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.

Functions used

Practise it

Related