Median in Excel Based on Criteria: Formulas and Examples

Formulas & functions · Updated November 21, 2026

In Excel for Microsoft 365, calculate a median for matching rows with =MEDIAN(FILTER(B2:B20,(A2:A20="East")*ISNUMBER(B2:B20))). This example returns the median of numeric values in B2:B20 where the corresponding category in A2:A20 is East; add more conditions inside FILTER to apply multiple criteria.

What a conditional median means

The median is the middle value after a set of numbers is sorted from smallest to largest. It is not the same as the average: an unusually large or small value can pull an average toward itself, while the median depends on the middle position. A conditional median applies that calculation only to rows that meet a rule, such as a particular region, employee, product, or date range.

For example, imagine a sales table with a region in column A and an order amount in column B. A median based on the East region answers: “What is the middle order amount among East orders?” It does not calculate a median for every region, and it does not average the qualifying orders.

Excel does not provide a standard MEDIANIFS function. Instead, combine MEDIAN with a filtering or array-calculation method. In current Microsoft 365 Excel, FILTER is usually the clearest choice because it first returns the values from qualifying rows and MEDIAN then finds their middle value.

Set up the data before writing the formula

Keep the criteria and values in aligned ranges: each row’s category should describe the value on that same row. In the examples below, A2:A20 contains region names and B2:B20 contains order amounts. Use matching range sizes; a criteria range with a different number of rows can cause an error or an incorrect result.

Check that the values being summarized are real numbers, not numbers stored as text. Format alone does not convert text into numeric values. A quick visual clue is that numbers stored as text may be left-aligned by default, though cell alignment can be customized. If the data came from a CSV or another system, convert it to numbers before relying on a statistical result.

The main formula uses ISNUMBER to include numeric amounts only. This intentionally excludes blank cells, text, and error values in the amount column while still including zero, which is a valid number. If zero means “no measurement” in your dataset rather than a genuine result, decide how to handle those rows and add an explicit condition to exclude them.

Calculate a median for one criterion

With region names in A2:A20 and numeric amounts in B2:B20, this formula returns the median amount for East. The criteria test and ISNUMBER test are multiplied, so a row is included only when both are true:

=MEDIAN(FILTER(B2:B20,(A2:A20="East")*ISNUMBER(B2:B20)))

For a concrete example, suppose the East amounts are 10, 20, and 100, and other regions have any values. The filtered East values are 10, 20, and 100; the result is 20. Their mean would be about 43.33, which illustrates why the median can be a more representative middle value when a dataset includes an outlier.

To avoid showing a FILTER no-results error when there are no numeric values for East, wrap the calculation in IFERROR. With the same layout, use:

=IFERROR(MEDIAN(FILTER(B2:B20,(A2:A20="East")*ISNUMBER(B2:B20))),"No matching numeric values")

The text message is useful in a report, but it means the cell will sometimes contain text rather than a number. If another formula needs to use the result numerically, you may prefer a blank or a separate status check instead of a message.

Use two or more criteria

Multiply condition tests to require that every condition be true. For instance, with region names in A2:A20, order status in C2:C20, and amounts in B2:B20, this returns the median for East orders marked Open:

=MEDIAN(FILTER(B2:B20,(A2:A20="East")*(C2:C20="Open")*ISNUMBER(B2:B20)))

Each parenthesized comparison produces a set of TRUE or FALSE results. Multiplication treats TRUE as 1 and FALSE as 0, so only rows where all tests are true remain eligible. Add another condition by multiplying in another aligned comparison, such as a product test or a date boundary.

For a range of dates, suppose dates are in D2:D20 and you want East orders dated from January 1 through January 31, 2026. With categories in A2:A20 and amounts in B2:B20, use:

=MEDIAN(FILTER(B2:B20,(A2:A20="East")*(D2:D20>=DATE(2026,1,1))*(D2:D20<DATE(2026,2,1))*ISNUMBER(B2:B20)))

The less-than February 1 boundary includes every time on January 31, even if the cells contain date-and-time values. DATE constructs actual Excel dates rather than relying on text that might be interpreted differently by regional settings. The formula assumes D contains valid Excel date values.

Use OR conditions and cell references

When a row may qualify under either of two alternatives, add the condition tests rather than multiplying them. For example, to include both East and West regions, with regions in A2:A20 and numeric amounts in B2:B20, use:

=MEDIAN(FILTER(B2:B20,((A2:A20="East")+(A2:A20="West"))*ISNUMBER(B2:B20)))

Addition acts like OR here: either region comparison can be true. Keep parentheses around the combined alternatives before multiplying by other requirements. This lets you combine an OR group, such as East or West, with an AND condition, such as a particular order status.

For a worksheet others will update, put the desired region in E2 rather than typing East into the formula. With regions in A2:A20, values in B2:B20, and the selected region in E2, use:

=IFERROR(MEDIAN(FILTER(B2:B20,(A2:A20=E2)*ISNUMBER(B2:B20))),"No matching numeric values")

Now changing E2 changes the selection without editing the formula. For more maintainable workbooks, consider converting the data range into an Excel Table: select a cell in the data, choose Insert > Table, and confirm whether the table has headers. Tables expand as rows are added, though formulas using structured references have different syntax from the range-based examples here.

How to interpret the result

For an odd number of qualifying values, the median is the one middle value. For an even number, Excel averages the two middle values. If the eligible values sorted are 4, 8, 12, and 20, the median is (8 + 12) / 2, or 10. That result may not appear as an actual entry in the source data, and that is normal.

The result describes only the rows that satisfy the formula’s conditions and pass its numeric-value test. It does not tell you how many rows qualified. In a dashboard or analysis, show the count alongside the median when sample size matters; a median based on two records and one based on two thousand records can look equally precise in a single cell.

Also check whether the selected group is meaningful. A typo such as “Eats” instead of “East,” trailing spaces in imported text, or inconsistent category labels can silently leave out expected rows. Cleaning labels or using data validation for category entry can prevent many such mistakes.

Excel versions and alternatives

FILTER is available in Microsoft 365 and Excel 2021 or later. In those versions, the FILTER formula is entered normally with Enter; it is a dynamic-array formula, so you do not need Ctrl+Shift+Enter. Excel for the web also supports dynamic arrays, although its ribbon and interface can differ from desktop Excel. On Mac, current Microsoft 365 Excel supports the same formula; the Windows-specific keyboard shortcuts discussed below may differ.

In older Excel versions without FILTER, use an array formula. With criteria in A2:A20 and values in B2:B20, this calculates the median for East while excluding nonnumeric amounts:

=IFERROR(MEDIAN(IF((A2:A20="East")*ISNUMBER(B2:B20),B2:B20)),"No matching numeric values")

In Excel 2019, Excel 2016, and other non-dynamic-array versions, confirm this formula with Ctrl+Shift+Enter rather than Enter alone. Excel displays braces around a correctly entered array formula in the formula bar; do not type the braces yourself. On a Mac, the exact key combination can vary by keyboard and Excel release, so use the version’s array-formula entry method if Ctrl+Shift+Enter is unavailable. In dynamic-array Excel, enter the same expression with Enter.

Another option is to filter the table manually, copy the visible numeric values to a separate range, and apply MEDIAN to that range. This can help with a one-off check but is less reliable for a report that must update automatically. A PivotTable is useful for summarizing many groups, but standard PivotTable value summaries do not offer a general median calculation in the same straightforward way as a conditional formula.

Common errors and how to fix them

  • FILTER returns #CALC!: There may be no rows matching the conditions. Use the IFERROR version to show a chosen message, and check spelling, spaces, and date boundaries.
  • The result seems too low: Check whether blank amount cells are being treated as zero in your chosen approach. The examples here explicitly require ISNUMBER, which excludes blank cells while retaining genuine zeros.
  • The formula returns an unexpected group: Confirm that each criteria range is aligned with the values range and that the intended AND or OR logic is used. Multiplication means all tests must pass; addition allows either alternative to pass.
  • You see #VALUE! or another range error: Verify that each array in FILTER has the same number of rows. Avoid comparing a full column in one part of a formula with a short range in another.
  • The older formula does not calculate: In a version without dynamic arrays, confirm the array formula with Ctrl+Shift+Enter. Also ensure the amount cells contain numbers rather than numeric-looking text.

Do not use IFERROR automatically to hide every problem in a complex workbook. It can make a report look clean while concealing a misspelled range or another genuine formula issue. During setup, test the formula without IFERROR so that unexpected errors remain visible; add a no-match message once you know the underlying calculation is correct.

Frequently Asked Questions

Does Excel have a MEDIANIFS function?
No. Excel does not provide a built-in MEDIANIFS function. In Microsoft 365 or Excel 2021 and later, combine MEDIAN with FILTER; in older versions, use an array formula such as MEDIAN(IF(…)) and confirm it with Ctrl+Shift+Enter.

Can I calculate a median with multiple conditions?
Yes. Multiply the condition tests inside FILTER to require all conditions. For alternatives, add the tests inside parentheses, then multiply by any conditions that must also be true.

Does MEDIAN include zero values?
Yes, zero is a numeric value and can affect the result. The examples include zero when it is present. If zero is a placeholder for missing data in your file, exclude it deliberately rather than assuming Excel will ignore it.

Why does my conditional median show an error?
Common causes are no matching rows, misaligned range sizes, or an older Excel version that cannot use FILTER. Check the criteria text and ranges; use IFERROR for an expected no-match result, or use the array formula in older Excel.

Can I calculate medians for every category?
Yes. In Microsoft 365, create a list of categories and use a formula that refers to each category cell, then fill it down. Alternatively, calculate one category at a time by changing a criteria input cell. Make sure each result’s category label and formula reference stay aligned.

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.