Excel Median IF: Multiple Criteria Made Easy

Formulas & functions · Updated November 22, 2026

To calculate a median in Excel based on multiple criteria, use an array formula with the MEDIAN and IF functions: =MEDIAN(IF((range1=criteria1)*(range2=criteria2), median_range)), entered with Ctrl+Shift+Enter (or just Enter in Excel 365). This formula filters the data to only the rows that meet all conditions, then finds the median of the matching values.

Why a Simple MEDIANIF Doesn’t Exist

Excel has AVERAGEIF and AVERAGEIFS for conditional averages, and SUMIF/SUMIFS for conditional sums, but there is no MEDIANIF or MEDIANIFS function. The MEDIAN function itself only takes a list of numbers or ranges. To apply a condition, you must build an array of values that meet the criteria and then pass that array to MEDIAN. This is a common gap that surprises many users, especially those familiar with other spreadsheet tools.

How the Array Formula Works

The formula uses array operations to evaluate each cell in the criteria ranges. The expression (range1=criteria1) returns an array of TRUE/FALSE values. Multiplying two such arrays with * performs a logical AND—each element becomes 1 if both conditions are TRUE, and 0 otherwise. When you multiply this by the median range, the FALSE rows become 0, and the TRUE rows keep their original values. The IF function then returns the actual value for TRUE rows and FALSE for the rest. MEDIAN ignores FALSE and 0? Actually, it ignores FALSE but includes 0 if it’s a real data value. To avoid including 0 as a legitimate value, the IF function returns FALSE for non-matching rows, and MEDIAN ignores logical values. This is the key trick: using IF to return FALSE (not 0) for excluded rows.

For example, with data in A2:A20 (Product), B2:B20 (Region), and C2:C20 (Sales), to find the median sales for Product “Widget” in Region “East”, use:

=MEDIAN(IF((A2:A20="Widget")*(B2:B20="East"), C2:C20))

In Excel 365 or Excel 2021, this formula works as a dynamic array and you can press Enter normally. In older versions, you must press Ctrl+Shift+Enter to enter it as an array formula. The result is the median of all sales values where both conditions are met.

Using MEDIAN with OR Logic

Sometimes you need a median where any one of several criteria is true (OR logic). Instead of multiplying, you add the condition arrays. For example, to find the median sales for either Product “Widget” or “Gadget”:

=MEDIAN(IF((A2:A20="Widget")+(A2:A20="Gadget"), C2:C20))

The plus sign acts as an OR, but be careful: if both conditions are TRUE for the same row, the sum becomes 2, which is still TRUE in the IF, so it works. However, if you have more than two OR conditions, ensure you don’t accidentally count a row twice—the IF treats any non-zero as TRUE, so it’s safe.

How to Choose Between MEDIAN IF and AGGREGATE

Another approach is the AGGREGATE function, which can calculate a median with conditions using the SMALL or LARGE functions, but it’s more complex. The array formula is straightforward and works in all Excel versions. However, if you have a very large dataset (tens of thousands of rows), array formulas can slow down your workbook. In that case, consider using a helper column that flags rows meeting the criteria, then use MEDIANIFS? No, that doesn’t exist. You could use the FILTER function in Excel 365 to create a filtered array, then apply MEDIAN: =MEDIAN(FILTER(C2:C20, (A2:A20="Widget")*(B2:B20="East"))). This is cleaner and faster in Excel 365. For older versions, the array formula is the standard solution.

Common Problems and How to Fix Them

  • Getting a #VALUE! error: This usually means you pressed Enter without Ctrl+Shift+Enter in older Excel. Re-enter with the correct key combination.
  • Incorrect results due to blank cells: If your median range contains blanks, they are ignored, which is fine. But if your criteria ranges have blanks, they won’t match anything, so those rows are excluded. That’s usually desired.
  • Text criteria with extra spaces: Use TRIM or ensure your criteria match exactly. For example, “Widget ” with a trailing space won’t match “Widget”.
  • Case sensitivity: The IF comparison is case-insensitive by default. If you need case-sensitive matching, use EXACT inside the IF, but that requires array entry.
  • Using whole column references (e.g., A:A): This works but can slow down calculation because it processes over a million rows. Limit ranges to your actual data.

Comparing MEDIAN IF with AVERAGEIFS

Many users wonder why they can’t just use AVERAGEIFS and swap in MEDIAN. AVERAGEIFS is a native function that handles criteria internally, but MEDIAN does not have that capability. The array formula is the only built-in way to get a conditional median without VBA or add-ins. If you need to calculate multiple medians for different criteria, you can copy the formula and change the criteria, but consider using a pivot table with the median as a custom calculation—though that requires the Power Pivot data model, which is more advanced.

Frequently Asked Questions

Can I use MEDIAN with wildcards like * in criteria?

No, the IF function does not support wildcards. If you need to match partial text, use ISNUMBER(SEARCH(“text”, range)) inside the IF. For example, to find the median for products containing “Wid”, use: =MEDIAN(IF(ISNUMBER(SEARCH("Wid", A2:A20)), C2:C20)) as an array formula.

How do I calculate the median for each group in a list?

You can use the array formula for each group, but a better approach is to create a pivot table and add the median as a calculated field. However, the median is not a standard pivot table summary. You can use Power Pivot to add a DAX measure like MEDIAN(Table[Sales]) and then filter by group. Alternatively, use the array formula in a helper column for each group.

Does this formula work in Google Sheets?

Yes, Google Sheets supports array formulas with MEDIAN and IF. You enter it with Ctrl+Shift+Enter (or Cmd+Shift+Enter on Mac) to create an array formula. The syntax is the same. However, Google Sheets also has a QUERY function that can compute medians with conditions more flexibly.

What if I have more than two criteria?

Simply multiply more condition arrays. For example, three criteria: =MEDIAN(IF((A2:A20="Widget")*(B2:B20="East")*(C2:C20>100), D2:D20)). The pattern extends to any number of conditions.

Why does my formula return 0 instead of the correct median?

This happens if you use multiplication without the IF wrapper, like =MEDIAN((A2:A20="Widget")*(B2:B20="East")*C2:C20). In that case, non-matching rows become 0, and MEDIAN includes those 0s, pulling the result toward zero. Always use the IF function to return FALSE for excluded rows, which MEDIAN ignores.

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.