How to calculate a median with a condition (MEDIANIF)

The median of only the rows matching a criterion — no MEDIANIFS exists, so here's the pattern.

The formula

How it works

AVERAGEIF and SUMIF have -IF versions; MEDIAN never got one in any of the three apps. FILTER fills the gap: it hands MEDIAN just the East values (150, 80, 200), whose middle is 150. Any condition FILTER accepts works — numeric thresholds, dates, multiple ANDed tests. The pre-dynamic-array MEDIAN(IF(...)) array formula does the same everywhere, entered with Ctrl+Shift+Enter in old versions.

Verified, not just documented

We ran =MEDIAN(FILTER(B2:B6,A2:A6="East")) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 150 — exactly the expected result. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 150 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.

Functions used

MEDIAN · FILTER — see full Excel, Google Sheets & LibreOffice compatibility for each.

Related recipes

• How to average a range that contains errors
• How to calculate the median (and when to prefer it over average)
• How to count unique values that match a condition
• How to FILTER by multiple criteria (AND / OR)
• How to find values that appear in both of two lists

Related comparisons

• ARRAYFORMULA vs dynamic arrays: the same idea, three dialects
• FILTER vs QUERY: Sheets' two ways to slice data
• FILTER vs VLOOKUP: return every match, not just the first