Median IFs in Google Sheets: How to Filter by Criteria

Google Sheets does not have a built-in MEDIANIFS function. To calculate a median for rows that meet conditions, combine MEDIAN with FILTER: if regions are in A2:A100, statuses in B2:B100, and the numbers to analyze in C2:C100, use =MEDIAN(FILTER(C2:C100,(A2:A100="North")*(B2:B100="Complete"))).

Why Google Sheets has no MEDIANIFS function

Sheets includes functions such as SUMIFS, COUNTIFS, and AVERAGEIFS for common conditional calculations, but it does not provide a corresponding MEDIANIFS function. A median is different from a sum or average: the values must be ordered before the middle value, or the middle pair, can be identified. The MEDIAN function handles that ordering, while FILTER selects the values that qualify.

This combination is flexible because the conditions can be changed without building a separate summary table. It also makes the calculation visible: FILTER determines which values are included, and MEDIAN summarizes that filtered set. The method works for one criterion or several, including text, dates, and comparisons such as greater than a threshold.

One important distinction: a median is not an average. The mean adds all the values and divides by their count, so a few unusually high or low values can move it substantially. The median is the middle value in sorted order, making it useful when a typical result matters more than the effect of outliers.

How MEDIAN and FILTER work together

FILTER returns the entries in a range whose corresponding condition is true. MEDIAN then calculates the midpoint of the returned numeric values. The values and condition ranges need to cover the same rows; otherwise, Sheets can return a range-size error or include the wrong records.

For multiple conditions in Google Sheets, FILTER can take separate condition arguments. However, multiplying Boolean tests inside one condition is also useful: TRUE and FALSE are treated as 1 and 0 in this calculation, so multiplication keeps rows where every test is true. This form is familiar to spreadsheet users and is also compatible with Excel’s FILTER syntax.

MEDIAN calculates the middle value, or the mean of the two middle values.
FILTER restricts the input to rows that satisfy the criteria.
The multiplied tests make the criteria an AND condition: every test must match.

FILTER returns an error when it finds no qualifying rows. That is not a sign that the median formula is wrong; it means there is no set of values to summarize. You can choose to display a helpful message for that case, as described below.

Build a conditional median with a small example

Suppose a service log has Region in column A, Status in column B, and Resolution days in column C. The rows contain these North-and-Complete resolution times: 12, 20, 28, and 36 days. The other rows include different regions or statuses and should not affect this calculation.

With the example data in A2:C8, use =MEDIAN(FILTER(C2:C8,(A2:A8="North")*(B2:B8="Complete"))). FILTER returns 12, 20, 28, and 36. The two middle values are 20 and 28, so their average is 24; the formula returns a median of 24 days.

To adapt the example to your sheet:

Identify the numeric range whose median you want, such as resolution days, prices, or test scores.
Identify the matching criteria ranges, keeping their first and last rows aligned with the numeric range.
Write each comparison using the appropriate column, such as region equals North or status equals Complete.
Multiply the conditions for an AND result, then check the result against a few matching rows manually.

Keep the formula ranges limited to the data rows when practical. Whole-column references may be convenient, but filtering an unnecessarily large range can make a busy spreadsheet slower and makes it easier to mismatch data or conditions.

Add criteria and choose AND or OR logic

For three or more AND criteria, multiply each test. For example, you might want completed North-region jobs assigned to a specific team. If region is in A2:A100, status is in B2:B100, team is in C2:C100, and elapsed days are in D2:D100, the median formula is =MEDIAN(FILTER(D2:D100,(A2:A100="North")*(B2:B100="Complete")*(C2:C100="Team 1"))). A row is included only if all three comparisons are true.

OR logic is different: a row should qualify if it meets either of two alternatives. If North or South regions qualify, but the status must still be Complete, use addition for the two region tests and multiply by the status test. With regions in A2:A100, statuses in B2:B100, and values in C2:C100, the formula is =MEDIAN(FILTER(C2:C100,((A2:A100="North")+(A2:A100="South")>0)*(B2:B100="Complete"))). The greater-than-zero test turns either matching region into a true condition.

For a numeric threshold, the same pattern applies. If scores are in C2:C100 and the category in A2:A100, use =MEDIAN(FILTER(C2:C100,(A2:A100="Exam")*(C2:C100>=70))) to find the median exam score of at least 70 in that category. Including a threshold on the values being summarized can be useful, but make sure it matches the question you intend to answer.

Use dates, cell references, and changeable criteria

Criteria do not need to be typed directly into the formula. A cell reference makes a calculation easier to reuse: put the chosen region in E1 and the status in F1. With region data in A2:A100, status data in B2:B100, and numeric values in C2:C100, use =MEDIAN(FILTER(C2:C100,(A2:A100=E1)*(B2:B100=F1))). Changing E1 or F1 updates the result without editing the formula.

Date criteria work when the source cells contain real spreadsheet dates rather than date-looking text. For example, if dates are in D2:D100, regions are in A2:A100, and the measured values are in C2:C100, this finds the median for North rows dated on or after January 1, 2025: =MEDIAN(FILTER(C2:C100,(A2:A100="North")*(D2:D100>=DATE(2025,1,1)))). DATE constructs a date value, avoiding ambiguity between regional date formats such as month/day/year and day/month/year.

For a threshold stored in a cell, compare against that cell instead of embedding a fixed number or date. This is particularly helpful when several people use the sheet or when the reporting period changes. Check that your comparison is inclusive or exclusive as intended: greater than or equal to includes the boundary, while greater than does not.

Handle empty results, text, and common errors

If no rows satisfy the criteria, FILTER returns an error because it has no values to return. Wrap the calculation in IFERROR when a readable message is preferable. With regions in A2:A100, statuses in B2:B100, and numeric results in C2:C100, use =IFERROR(MEDIAN(FILTER(C2:C100,(A2:A100="North")*(B2:B100="Complete"))),"No numeric matches").

IFERROR also catches other errors, so it can conceal problems such as inconsistent range sizes. If you are checking a new formula, first try it without IFERROR. Confirm that the filtered rows are the ones you expect, then add the wrapper if an empty-match message is useful. A message such as “No numeric matches” is more informative than returning zero, which could be mistaken for a real median.

MEDIAN ignores blank cells and text in a referenced range, but data stored as text can still cause confusion: a value that looks like 25 may not be treated as the number 25. If the result seems wrong, inspect the source cells, remove stray spaces, and convert numeric text into actual numbers. Also verify that the value range contains measurements rather than labels, and that every condition range starts and ends on the same rows.

#N/A: FILTER may have found no matching rows. Check spelling, spaces, and the criteria values.
Range-size error: Make the filtered values and every condition array the same height.
Unexpected median: Inspect the qualifying records and check whether apparent numbers are stored as text.
Wrong rows included: Check the logic. Multiplying tests means AND; adding tests with a greater-than-zero check creates OR logic.

Alternatives and spreadsheet compatibility

A helper column is a reasonable alternative when a formula with several conditions is hard to audit. In each data row, mark whether it meets the criteria, then filter or copy the corresponding numeric values and calculate their median. This takes more setup and may require updating the helper logic when criteria change, but it can make the included records easy for colleagues to inspect.

A pivot table is useful for grouped summaries such as counts, sums, or averages, but it is not a direct substitute for a conditional median in every setup. For a straightforward, live median by conditions, MEDIAN with FILTER is usually clearer. Google Sheets’ QUERY function offers several common aggregations, but it does not provide a general median aggregation in its query language.

The formulas here are written for Google Sheets in a web browser. Excel for Microsoft 365 and Excel 2021 or later also support FILTER and MEDIAN, though the function details and available tools can differ between programs. In either program, formulas normally use commas between arguments in the examples shown; spreadsheet locale settings may require semicolons instead. If Sheets reports a formula parse error, check the locale’s separator convention and the quotation marks around text criteria.

Frequently Asked Questions

Does Google Sheets have a MEDIANIFS function?
No. Use MEDIAN with FILTER to calculate a median for rows meeting one or more criteria.

How do I calculate a median based on one condition?
With categories in A2:A100 and numbers in B2:B100, use =MEDIAN(FILTER(B2:B100,A2:A100="North")). Replace North with the category you need.

How do I apply multiple conditions?
Multiply the condition tests inside FILTER’s include expression. Multiplication requires all tests to match; use an OR expression when any of several alternatives should qualify.

What if there are no matching rows?
FILTER returns an error when it finds no matches. You can use IFERROR to show a message, but check the criteria and ranges first so the wrapper does not hide a formula mistake.

Does the median formula include blank cells?
MEDIAN does not treat blank cells in the filtered range as numeric values. Check for numbers stored as text if the returned median does not match your expectations.

Can I use a cell as the criterion?
Yes. Refer to the cell in the comparison, such as A2:A100=E1. This lets you change the selected category without rewriting the formula.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.