MEDIAN IF in Excel: Find the Median With Conditions

To get a MEDIAN IF in Excel, you wrap a condition inside MEDIAN, because Excel has no MEDIANIF function. In Microsoft 365 or Excel 2021, =MEDIAN(FILTER(values, condition)) is the simplest way. In older versions, =MEDIAN(IF(condition, values)) does the same job as an array formula. This guide is for anyone who needs a "typical" value for one group, such as the median salary in one department or the median deal size for one team.

Why use a median with conditions?#

The median is the middle value when the numbers are sorted, so one very large or very small number doesn't drag it around. That makes it a better "typical" value than the average for pay, prices, deal sizes and response times. The guide to average vs median explains when each one fits.

The problem is that Excel gives you AVERAGEIF and MAXIFS and MINIFS, but no MEDIANIF. So you build one. All examples use this table in A1:C8:

MEDIAN IF with FILTER (Microsoft 365 and Excel 2021)#

FILTER returns only the values that meet a condition, and MEDIAN finds the middle of what is left. To get the median Sales deal:

=MEDIAN(FILTER(C2:C8,B2:B8="Sales"))

The result is 1,650. FILTER keeps 1,200, 4,500, 900 and 2,100. Sorted, they are 900, 1,200, 2,100, 4,500. With an even count, the median is the average of the two middle values: (1,200 + 2,100) / 2 = 1,650.

Compare that with the average Sales deal, which is 2,175. The single 4,500 deal pulls the average up, but not the median. For Support, the same formula with "Support" returns 800, the middle of 700, 800 and 1,500.

FILTER needs Microsoft 365 or Excel 2021 or later. The guide to the FILTER function and the FILTER function reference cover its arguments.

MEDIAN IF with IF (every version)#

In older Excel, IF does the filtering. It returns the deal size when the team matches and FALSE when it doesn't, and MEDIAN ignores the FALSE values:

=MEDIAN(IF(B2:B8="Sales",C2:C8))

The result is again 1,650. This is an array formula: it works on a whole range at once.

• In Microsoft 365 and Excel 2021, press Enter as usual.
• In Excel 2019 and earlier, press Ctrl+Shift+Enter (Mac: ⌃⇧Return or ⌘⇧Return). Excel shows curly braces { } around the formula. Don't type them yourself.

If you press plain Enter in an old version, the formula may look fine but check only one row, so the result is wrong. The IF function guide explains IF's arguments if the structure is new to you.

How to find the median with multiple criteria#

For two or more conditions, multiply the tests. Each test gives TRUE or FALSE, Excel treats them as 1 and 0, and the product is 1 only when every test is TRUE.

The median Sales deal over 1,000:

=MEDIAN(FILTER(C2:C8,(B2:B8="Sales")*(C2:C8>1000)))

The result is 2,100: the kept values are 1,200, 4,500 and 2,100, and 2,100 is the middle one. The IF version uses the same product as its test:

=MEDIAN(IF((B2:B8="Sales")*(C2:C8>1000),C2:C8))

For OR logic, add the tests instead. (B2:B8="Sales")+(B2:B8="Support") keeps rows from either team. This is the same pattern that AVERAGEIFS with several conditions handles for averages, but written by hand.

How to build a median table for every group#

To show the median for each team, list the teams once and point the formula at each name. In Microsoft 365, type =UNIQUE(B2:B8) in E2. It spills Sales and Support into E2:E3. Then in F2:

=MEDIAN(FILTER($C$2:$C$8,$B$2:$B$8=E2))

Fill it down to F3 (Windows: Ctrl+D; Mac: ⌘D). You get 1,650 for Sales and 800 for Support. The $ signs keep the data ranges fixed while E2 moves to E3.

Blanks, text and errors in MEDIAN IF#

Empty cells are the most common cause of a wrong median. Both FILTER and IF return a blank Deal cell as 0 when its row matches, and MEDIAN counts that 0. Add a test that skips blanks:

=MEDIAN(FILTER(C2:C8,(B2:B8="Sales")*(C2:C8<>"")))

Watch for these too:

When to use MEDIAN IF instead of AVERAGEIF#

Use the median when a few extreme values would mislead you, and the average when every unit counts equally. Pay bands, house prices and deal sizes usually have a long tail of large values, so the median gives a fairer "typical" number. Totals, budgets and average units per order usually need the average. The article on MEDIAN and MODE shows both side by side on the same data.

A quick check helps you decide. Work out both numbers for the group, here 1,650 for the median and 2,175 for the average. If they are close, either one tells the same story. If the average is much higher than the median, a few large values are pulling it up, and the median is the fairer number to report. Say which one you used in the report, so readers don't compare a median with someone else's average.

Practise it#

A conditional median is two skills joined together: MEDIAN on its own, and FILTER to pick the rows. Practise MEDIAN and MODE in a live spreadsheet, then practise the FILTER function: you type each formula and ExcelReady checks it instantly. Start with the Sales median in the exercise on this page.