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
- Excel Subtotals: How to Add Them Step by Step
- Excel Hourly Schedule Template: How to Make One
- Excel: How to Add Lines in a Cell (3 Easy Ways)
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Table Filter Multiple Columns: How It WorksYes. In an Excel table, you can filter multiple columns at the same time by choosing a filter…
- How to Paste a Formula in Excel and Keep It WorkingTo paste a formula in Excel, select the cell that contains it, press Ctrl+C, select the destination cell,…
- Cash Flow Formula in Excel: Examples and How to Use ItThe basic cash flow formula in Excel is cash inflows minus cash outflows. If inflows are in B2…
- How to Call a Function in Snowflake (Step-by-Step)Calling a function in Snowflake means executing a built-in or user-defined function (UDF) to return a value, transform…
- Excel Formula Bar Disappeared? How to Show It AgainIf the Excel formula bar disappeared, turn it back on from View > Show > Formula Bar in…
- Filter Duplicates in Excel: Keep One or Remove All?To remove duplicate entries in Excel for Microsoft 365 on Windows, select your data and choose Data >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.