Conditional Median in Excel: Formulas and Examples

Formulas & functions · Updated November 22, 2026

In Excel for Microsoft 365, calculate a conditional median with MEDIAN and FILTER: filter the numeric values to the rows that meet your condition, then take the median of the remaining values. For example, with regions in A2:A8 and amounts in B2:B8, =MEDIAN(FILTER(B2:B8,A2:A8="East")) returns the median amount for East.

What a conditional median tells you

A median is the middle value after numbers are sorted from smallest to largest. If there is an odd number of values, it is the one in the center; with an even number, it is the average of the two center values. A conditional median applies that calculation only to records that meet a rule, such as a particular region, product, employee, or date range.

This is useful when a large dataset contains several groups and you want a typical value for just one of them. For instance, a median delivery time for one shipping method can be more representative than an overall median that mixes several methods. The median is less affected than the average by a few unusually high or low values, although it does not describe every aspect of the distribution.

Excel does not have a built-in function named MEDIANIF. The usual approach is to combine MEDIAN with a function that selects matching values. In current Excel, FILTER is a convenient option because it returns only the entries that meet a logical test.

Basic formula with FILTER

Assume A2:A8 contains region names and B2:B8 contains numeric amounts. The formula =MEDIAN(FILTER(B2:B8,A2:A8="East")) returns 25 when the East amounts are 12, 18, 25, 40, and 100. Those five values are already enough to verify the result: after sorting, 25 is the middle value.

Enter the formula in an empty cell and press Enter. In Excel for Microsoft 365, FILTER returns a dynamic array of matching values, and MEDIAN calculates over that result. You do not need to select a range of output cells or press a special array-formula shortcut. Excel for the web and current Excel for Mac also support these functions; the interface may differ, but the formula is the same.

To make the condition changeable, put a region name in D2. With regions in A2:A8, amounts in B2:B8, and the requested region in D2, use =MEDIAN(FILTER(B2:B8,A2:A8=D2)). Changing D2 from East to West updates the result without editing the formula. This is handy for a summary sheet with a dropdown or a set of group labels.

How FILTER selects the values

FILTER takes an array to return and an include test of the same height or width. In the example, B2:B8 is the array of amounts; A2:A8=”East” creates a TRUE-or-FALSE test for each corresponding row. FILTER keeps the amount wherever the test is TRUE. MEDIAN then ignores the original row order and finds the center of the filtered numbers.

The paired ranges need to line up. If the amount range is B2:B8, the condition range should normally cover the same rows, such as A2:A8. A range that begins on a different row can compare the wrong records or cause a size mismatch error. When extending a formula, check that both ranges include the same records and that headers are excluded.

FILTER is available in Excel for Microsoft 365 and Excel 2021 or later. In older perpetual versions that do not include FILTER, use the array-formula approach described below. If you share a workbook with people using older Excel, consider their version before relying on a dynamic-array formula.

Using more than one condition

You can combine tests inside FILTER. Multiplying logical tests works like AND: a row is included only if every test is TRUE. Suppose A2:A8 contains region, B2:B8 contains amounts, and C2:C8 contains a status. If the East records marked Complete have amounts 12, 25, 40, and 100, use =MEDIAN(FILTER(B2:B8,(A2:A8="East")*(C2:C8="Complete"))). The result is 32.5, the average of the two middle values, 25 and 40.

For an OR condition, add logical tests instead of multiplying them. For example, to include either East or West, use =MEDIAN(FILTER(B2:B8,(A2:A8="East")+(A2:A8="West"))). If those are the only two possible region values, this selects every row. In real data, an OR condition is most useful when the alternatives represent only some of the categories in a larger list.

You can also use comparison operators for numeric or date conditions. With dates in A2:A100 and amounts in B2:B100, a start date in D2 and an end date in E2, use =MEDIAN(FILTER(B2:B100,(A2:A100>=D2)*(A2:A100<=E2))). The result includes rows on both boundary dates. Confirm that the dates are stored as Excel dates rather than text; text that merely looks like a date may not compare as expected.

Tables and changing datasets

For a dataset that grows over time, an Excel Table can be easier to maintain than fixed cell ranges. Select a cell in the data and choose Insert > Table, then give the table meaningful column headers. Assume the table is named Sales and its columns are Region and Amount, with the region to summarize in D2. The formula =MEDIAN(FILTER(Sales[Amount],Sales[Region]=D2)) returns the median for the selected region.

Structured references expand as rows are added to the table, so the formula can include new records without manually changing A2:A8-style ranges. Use the actual table and column names from your workbook; spaces and punctuation in headers affect how Excel writes structured references. Tables are especially helpful when formulas and source data live on the same worksheet, but a normal range formula remains perfectly suitable for a stable dataset.

No matches, blanks, and data errors

If no row meets the condition, FILTER normally returns a #CALC! error because the result is empty. MEDIAN cannot calculate a median from an empty set. To show a readable message when a requested category does not exist, assume regions are in A2:A8, amounts in B2:B8, and the selected region is in D2. Use =IF(COUNTIF(A2:A8,D2)=0,"No matching records",MEDIAN(FILTER(B2:B8,A2:A8=D2))). The result is the median when matches exist and the message otherwise.

That check only establishes that the category appears in column A; it does not prove that its corresponding amounts are numeric. MEDIAN ignores text and empty cells in a referenced range, while zeros are valid numbers and are included. Consequently, a matching row with a blank amount does not contribute a number, but a zero amount can shift the median. If all matching amounts are blank or text, there is no numeric median to calculate.

Dates or amounts imported as text are a common source of confusing results. Check a few cells with Excel’s number formats and, where appropriate, convert the source values to real numbers or dates. Also check for trailing spaces or inconsistent spelling in category labels: East and East followed by a space are not the same text value for an exact comparison. If the formula returns an unexpected result, inspect the filtered records before changing the median calculation.

Older Excel without FILTER

In Excel versions without FILTER, MEDIAN can evaluate an IF expression as an array formula. Assume regions are in A2:A8 and amounts are in B2:B8. Enter =MEDIAN(IF(A2:A8="East",B2:B8)). In legacy Excel for Windows, confirm it with Ctrl+Shift+Enter rather than Enter; Excel displays braces around the formula in the formula bar to indicate an array formula. Do not type those braces yourself.

In Excel for Mac editions that require legacy array entry, use Control+Shift+Return to confirm it. The exact behavior depends on the Excel version: current Microsoft 365 versions support dynamic arrays, so a normal Enter is generally sufficient. The IF expression supplies amounts for matching rows and FALSE for the others; MEDIAN ignores those FALSE results when calculating the median.

Older array formulas can be less intuitive to edit and may behave differently from dynamic-array formulas, so identify the workbook’s target Excel versions before choosing this method. If users have Microsoft 365 or Excel 2021 and later, FILTER is usually easier to read and maintain. Do not replace the condition with a formula that averages matching values: an average and a median answer different questions.

Median versus average and other approaches

A conditional median and conditional average are not interchangeable. If the matching values are 12, 18, 25, 40, and 100, their median is 25, while their average is 39. The high value of 100 pulls the average upward, but it does not move the middle observation. Use a median when the central ranked value is the intended summary; use an average when the arithmetic mean is the measure you need.

Excel’s AVERAGEIF and AVERAGEIFS functions calculate conditional averages, not medians. They may be simpler for a mean, but there is no equivalent built-in MEDIANIF function. PivotTables can summarize and filter data interactively, but their standard value summaries do not provide a conditional median in the same straightforward way as a formula. For a repeatable result in a worksheet, MEDIAN with FILTER is usually the direct method.

Frequently Asked Questions

Does Excel have a MEDIANIF function?
No. Use MEDIAN with FILTER in Microsoft 365 or Excel 2021 and later, or use a legacy array formula in versions without FILTER.

How do I calculate a conditional median with two criteria?
With matching ranges for each field, multiply the tests inside FILTER to require both conditions. For example, the region test multiplied by the status test includes only rows where both are true.

Why does my formula return #CALC! or #NUM!?
There may be no matching rows, or there may be no numeric values among the matches. Check the condition labels and the matching values. FILTER’s empty result causes an error unless you handle the no-match case.

Does the median include zero values?
Yes. Zero is numeric, so MEDIAN includes it. Blank cells and text values are ignored, which can produce a different result if imported numbers are stored as text.

Can I use a conditional median in a PivotTable?
PivotTables are useful for grouping and filtering, but their usual summary options do not offer a simple built-in median calculation. A worksheet formula using FILTER and MEDIAN is often the clearest option for this task.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.