Function Problems

How to Calculate a Conditional Median in Excel with MEDIAN and IF

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to calculate the median of a specific column based on a text condition in another column, but the traditional MEDIAN and IF formula is not working as expected.

Product: Excel
Device & OS: not provided
Scenario: Calculating statistical values (median) based on specific text conditions matching a reference cell.
Observed behavior: While AVERAGEIFS works for calculating averages, combining MEDIAN and IF fails to produce the correct conditional median result.

Before you start

Ensure you are using a version of Excel that supports dynamic arrays (such as Excel 365 or Excel 2021) if you plan to use the recommended FILTER function method.

Solution 1Recommended

Use the MEDIAN and FILTER Dynamic-Array Formula

The most efficient way to calculate a conditional median in modern Excel is by combining the MEDIAN, FILTER, ISNUMBER, and SEARCH functions.

This dynamic-array approach avoids the need for legacy array formulas (Ctrl+Shift+Enter) and reliably handles partial text matches.

It is highly recommended to use bounded ranges instead of entire column references to prevent Excel from slowing down.

Select the target cell

Click on the blank cell where you want the conditional median result to appear.

Enter the FILTER and SEARCH formula

Type the formula: =MEDIAN(FILTER(G2:G1000,ISNUMBER(SEARCH(L6,A2:A1000)))). In this formula, G2:G1000 is the range of numbers you want to find the median for, A2:A1000 is the criteria range, and L6 contains the text you are searching for.

Execute the calculation

Press Enter. Excel will automatically filter the values in column G based on the text criteria in column A, and then return the median of those filtered values.

Performance Tip: Using exact bounds like G2:G1000 instead of full columns (G:G) significantly improves calculation speed.

Solution 2

Use MEDIAN and IF Array Formula (For Older Excel Versions)

If you do not have access to dynamic arrays in older versions of Excel, you must use a traditional array formula.

Calculate Conditional Medians Seamlessly with WPS Office

WPS Office Spreadsheet fully supports advanced array formulas, including MEDIAN, FILTER, and IF, allowing you to perform complex statistical analysis without compatibility issues.

1. Open your dataset: Launch WPS Spreadsheet and open your workbook containing the data you need to analyze.
2. Input the formula: Select a blank cell and type the formula =MEDIAN(FILTER(G2:G1000,ISNUMBER(SEARCH(L6,A2:A1000)))).
3. Get the result: Press Enter to instantly calculate the conditional median. WPS Office handles the dynamic array processing automatically.

Fully compatible with Microsoft Excel formulas and functions, including modern dynamic arrays.Seamlessly executes complex functions like FILTER, SEARCH, and MEDIAN without syntax modification.Lightweight software with a familiar, easy-to-use interface.Free to download and use for everyday spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my MEDIAN and IF formula return an error or an incorrect result?

If you are using an older version of Excel, MEDIAN and IF formulas must be entered as array formulas by pressing Ctrl + Shift + Enter. If you only press Enter, Excel may return a #VALUE! error or an incorrect number.

Is there a built-in MEDIANIFS function in Excel?

No. Unlike AVERAGEIFS or SUMIFS, Excel does not have a built-in MEDIANIFS function. You must combine the MEDIAN function with IF or FILTER to achieve conditional medians.

Why should I avoid using entire column references like G:G in array formulas?

Using entire column references forces Excel to process over a million rows per calculation. This can severely slow down your workbook's performance. It is best practice to use bounded ranges like G2:G1000.

Can I use an exact match instead of a partial text match for the conditional median?

Yes. If you need an exact match rather than searching for text within a string, you can simplify the formula to =MEDIAN(FILTER(G2:G1000, A2:A1000=L6)).

Excel & Data Problems