How to calculate a median with criteria in Excel (MEDIAN IF)
Formula (Excel and Google Sheets)
=MEDIAN(FILTER(C2:C10,B2:B10=F1))Filter the values first, then take the median: =MEDIAN(FILTER(C2:C10,B2:B10=F1)).
- Result on the example
- $118,000 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 MEDIANIF function. FILTER keeps the five Engineering salaries, and MEDIAN takes the middle one of those: $118,000. Change F1 and the median follows.
=MEDIAN(FILTER(C2:C10,B2:B10=F1))Result118000
C2:C109 rows × 1 col: $118,000, $72,000, $131,500, $104,000, …A range: 9 cells in one column.
B2:B109 rows × 1 col: "Engineering", "Sales", "Engineering", "Engineering", …A range: 9 cells in one column.
F1"Engineering"The value in F1.
B2:B10=F1{9 rows × 1 col} TRUE, FALSE, TRUE, TRUE, …The = operator tests whether the two sides are equal.
FILTER(C2:C10,B2:B10=F1){5 rows × 1 col} 118000, 131500, 104000, 156000, …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
C2:C109 rows × 1 col: $118,000, $72,000, $131,500, $104,000, … - condition1
B2:B10=F1{9 rows × 1 col} TRUE, FALSE, TRUE, TRUE, …
- range
MEDIAN(FILTER(C2:C10,B2:B10=F1))118000MEDIAN: Middle value of the numbers.- number1
FILTER(C2:C10,B2:B10=F1){5 rows × 1 col} 118000, 131500, 104000, 156000, …
- number1
In Google Sheets
The same formula works in Google Sheets: =MEDIAN(FILTER(C2:C10,B2:B10=F1)). We ran it in Google Sheets mode on the same data and it returns $118,000.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =MEDIAN(IF(B2:B10=F1,C2:C10))Excel only | $118,000 | Works in older Excel with Ctrl+Shift+Enter: IF returns FALSE for other departments, and MEDIAN ignores FALSE. |
Common mistakes
- If no row matches, FILTER returns #CALC! and MEDIAN passes it on. Add a fallback:
FILTER(C2:C10,B2:B10=F1,""). - In the IF version, giving IF a third argument of 0 adds zeros for every other department and drags the median down.
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 a median with criteria in Excel (MEDIAN IF)
F2, calculate the median salary for the department in F1.Questions
How do I use two conditions?
Multiply the tests: =MEDIAN(FILTER(C2:C10,(B2:B10=F1)*(C2:C10>60000))).
Does this work in Google Sheets?
Yes. Google Sheets has FILTER and MEDIAN; its FILTER takes no fallback argument.
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.