Median by Group

Excel 365Array alt

There’s no MEDIANIF function, so the median for one group — the typical West-region order, median salary by department — takes MEDIAN + FILTER (Excel 365) or a classic MEDIAN(IF()) array formula.

Quick formula: for the median of column B where group (A) equals E2 (Excel 365):

=MEDIAN(FILTER(B2:B100, A2:A100=E2))

FILTER keeps one group’s values; MEDIAN finds their middle value.

Functions used (tap for the full reference guide):

MEDIAN FILTER IF

The example

Order amounts by region. The median West order.

	A	B
1	Region	Amount
2	West	$120
3	East	$90
4	West	$180
5	West	$150
6	West median:	$150

The formula

The median West order (middle of 120, 150, 180):

=MEDIAN(FILTER(B2:B5, A2:A5="West")) // {120,150,180} → 150

How it works

Filter to the group, then take the median:

FILTER(B2:B5, A2:A5="West") returns just West’s amounts: {120, 180, 150}.
MEDIAN(…) sorts them and returns the middle value — 150 (with an even count, it averages the two middles).
The median resists outliers far better than the average — one huge order won’t skew it.
In older Excel, use the array form =MEDIAN(IF(A2:A5="West", B2:B5)) with Ctrl+Shift+Enter.

Build a per-group table: spill a UNIQUE list of regions, then put the MEDIAN(FILTER()) beside each — an instant median-by-group summary.

Try it: interactive demo

Live demo

Pick a region; see its median order.

Region (E2)

Median:

Variations

Legacy array formula

Any version — Ctrl+Shift+Enter:

=MEDIAN(IF(A2:A100=E2, B2:B100))

Median with two conditions

Filter on more than one field:

=MEDIAN(FILTER(B2:B100, (A2:A100=E2)*(C2:C100="Widget")))

Average by group instead

When you want the mean:

=AVERAGEIF(A2:A100, E2, B2:B100)

Pitfalls & errors

#CALC! if no rows match. FILTER returns nothing for an unknown group. Add an if_empty: =MEDIAN(FILTER(B:B, A:A=E2, "")) — but note MEDIAN of "" errors, so guard with IFERROR.

Median vs average. Median is the middle value (robust to outliers); average is the mean. They answer different questions — choose deliberately.

FILTER needs Excel 365/2021. Older versions need the MEDIAN(IF()) array formula, entered with Ctrl+Shift+Enter.

Practice workbook

📊

Download the free Median by Group practice workbook

Orders by region with the median-by-group result, the legacy MEDIAN(IF()) array and two-condition variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate the median for a group in Excel?

There's no MEDIANIF, so use =MEDIAN(FILTER(values, group=g)) in Excel 365, or the array formula =MEDIAN(IF(group=g, values)) entered with Ctrl+Shift+Enter in older versions.

Why use median instead of average by group?

The median is the middle value and resists outliers, so a few extreme records won't skew it the way they skew the average. Use median for typical-value questions.

How do I find the median with multiple conditions?

Multiply the conditions inside FILTER: =MEDIAN(FILTER(values, (group=g)*(type=t))).

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Average by group · Percentile & quartile · Standard deviation

Function references: MEDIAN · FILTER · IF