# How to calculate a median with criteria in Excel (MEDIAN IF)

Filter the values first, then take the median: `=MEDIAN(FILTER(C2:C10,B2:B10=F1))`.

**Excel:** `=MEDIAN(FILTER(C2:C10,B2:B10=F1))` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Employee | Department | Salary |  | Department | Engineering |
| E-01 | Engineering | 118000 |  | Median salary |  |
| E-02 | Sales | 72000 |  |  |  |
| E-03 | Engineering | 131500 |  |  |  |
| E-04 | Engineering | 104000 |  |  |  |
| E-05 | Sales | 68500 |  |  |  |
| E-06 | Engineering | 156000 |  |  |  |
| E-07 | Support | 54000 |  |  |  |
| E-08 | Engineering | 112500 |  |  |  |
| E-09 | Sales | 91000 |  |  |  |

Formula in F2: `=MEDIAN(FILTER(C2:C10,B2:B10=F1))`

Result: $118,000

## 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.

## Other ways

- `=MEDIAN(IF(B2:B10=F1,C2:C10))`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/median-if