30 Advanced Excel Tips for 2026

Excel Champs / Excel Formulas List / How to Calculate MEDIAN IF in Excel
By Puneet Gogia (Microsoft MVP)

How to Calculate MEDIAN IF in Excel

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

Excel has no MEDIANIF function, so you combine MEDIAN with IF as an array formula: =MEDIAN(IF(A2:A12="Day 1",B2:B12)), entered with Ctrl+Shift+Enter on Excel 2019 or earlier. The IF builds an array containing only the values where the condition is met, and MEDIAN takes the middle of those.

In Excel 365 and 2021 you don’t need the special entry — plain Enter works — and there’s a cleaner option: =MEDIAN(FILTER(B2:B12,A2:A12="Day 1")), which reads much closer to what you mean. For two conditions, multiply them: =MEDIAN(IF((A2:A12="Day 1")*(C2:C12="North"),B2:B12)), or in 365, =MEDIAN(FILTER(B2:B12,(A2:A12="Day 1")*(C2:C12="North"))).

If no rows match, FILTER returns #CALC! and the array version returns #NUM! — wrap in IFERROR if that’s possible.

To write the MEDIAN IF formula in Excel, you need to use the MEDIAN function combined with the IF function. First, you need to use the MEDIAN function, and then, to create a condition, you need to enter the IF function.

In the IF, refer to the range on which you want to test the condition. After that, refer to the range where you have the data. In the end, enter the formula as an array formula (in Excel 2019 and older versions).

In Excel, there isn’t a built-in function specifically called MEDIANIF, unlike the AVERAGEIF or SUMIF functions.

However, suppose you need to calculate the median for a subset of data based on certain criteria. In that case, you can write a MEDIANIF formula using an array formula or with the help of other functions like IF.

How to Calculate MEDIAN IF in Excel
median-with-if

What is a Median – The value divides a list of numbers into two parts. In other words, half the numbers are less than the median, and half are greater. The median is the middle if there’s an odd number of values. If there’s an even number, the median is the average of the two middle numbers. It’s a handy way to find the middle point in a data set.

In this tutorial, we will learn to write a formula to calculate a MEDIANIF.

Calculate MEDIANIF in Excel with a Conditional Formula

You can use the below steps:

calculate-median-if
  1. First, enter the MEDIAN function in a cell.
  2. After that, enter the IF function with the MEDIAN.
  3. Now, in the (logical_test) argument, refer to the range A1:A12.
  4. From here, in the same argument, enter the equal to operator and enter “Day 1” to use as a condition to test.
  5. Next, in the (value_if_true) argument of IF, refer to the range B1:B12, where you have the data to calculate the MEDIAN.
  6. In the end, enter the closing parentheses to close the functions. And use Ctrl + Shift + Enter to get the result from the formula (in Microsoft 365 and Excel 2021, just press Enter).
=MEDIAN(IF(A1:A12="Day 1",B1:B12))

Regular formulas in Excel calculate a single value from multiple values. But, array formulas can perform various calculations and handle arrays of values as input and output. By pressing Ctrl+Shift+Enter, you tell Excel that the formula should be evaluated as an array formula, which allows it to process multiple values. In Microsoft 365 and Excel 2021, every formula handles arrays on its own, so pressing Enter is enough.

How this Formula Works

To understand this formula, you need to understand that a formula entered as an array formula works on the entire range instead of a single cell. You need to break this formula into two parts to understand it.

understands-the-formula

In the FIRST PART, IF(A1:A12=”Day 1″,B1:B12), uses the IF function. This function tests a condition for each cell in the range A1:A12 to see if it contains the text “Day 1”.

If a cell in this range contains “Day 1”, then the corresponding value in the same row from the range B1:B12 is included in an array.

And if a cell in the range A1:A12 does not contain “Day 1,” IF returns FALSE for that row instead of the value from the range B1:B12, and MEDIAN ignores FALSE values in the array.

condition-to-test-in-first-part

After that, in the second argument of the IF function, you refer to the range B1:B12, where we have the data to calculate the median.

referred-to-range-in-second-part

In the SECOND PART, use the MEDIAN function. This function calculates the median value of the array created by the IF function.

The median is the middle value in a set of values. If the set has an even number of values, the median is the average of the two middle numbers.

But Can I Use Helper Columns for MEDIANIF?

Yes, if you prefer not to use array formulas, you can use a helper column to filter out the values first based on the criterion and then apply the MEDIAN function to the filtered values.

In a new column (say C), you can enter the following formula next to each row in your dataset and then drag it down to apply it to other rows.

=IF(A1="Day 1",B1,"")

Now, you can simply calculate the median on this helper column:

=MEDIAN(C1:C12)

This will return the median of all values that meet the criteria without requiring an array formula. MEDIAN ignores the empty text (“”) that the helper formula returns for the other rows.

Write a User-Defined Function for MEDIANIF

Below is an example of a VBA function that you can use to calculate the median of a range of numbers based on a specific condition in another range.

All you need to do is copy the code below and paste it into a new module in the Visual Basic editor: Developer Tab > Visual Basic Editor > Insert Module > Paste the Code > Enter the Formula in the Worksheet.

=MEDIANIF(dataRange, criteriaRange, criterion)
Function MEDIANIF(dataRange As Range, criteriaRange As Range, criterion As Variant) As Variant
Dim cell As Range
Dim medianArray As Collection
Set medianArray = New Collection

' Collect all numbers that meet the criterion
Dim i As Long
For i = 1 To dataRange.Cells.Count
If criteriaRange.Cells(i).Value = criterion Then
medianArray.Add dataRange.Cells(i).Value
End If
Next i

' Handle case where no data meets the criterion
If medianArray.Count = 0 Then
MEDIANIF = "No data matches criteria"
Exit Function
End If

' Copy the collection to an array for sorting
Dim sortedArray() As Double
ReDim sortedArray(1 To medianArray.Count)
For i = 1 To medianArray.Count
sortedArray(i) = medianArray(i)
Next i

' Sort the array
Call QuickSort(sortedArray, LBound(sortedArray), UBound(sortedArray))

' Calculate median
Dim mid As Integer
mid = UBound(sortedArray) \ 2
If UBound(sortedArray) Mod 2 = 0 Then
' Even number of items; return average of two middle elements
MEDIANIF = (sortedArray(mid) + sortedArray(mid + 1)) / 2
Else
' Odd number of items; return the middle element
MEDIANIF = sortedArray(mid + 1)
End If
End Function

' Utility function for sorting the array
Sub QuickSort(arr As Variant, first As Long, last As Long)
Dim pivot As Double, temp As Double
Dim i As Long, j As Long
If first >= last Then Exit Sub
pivot = arr((first + last) \ 2)
i = first
j = last
While i <= j
While arr(i) < pivot And i < last
i = i + 1
Wend
While arr(j) > pivot And j > first
j = j - 1
Wend
If i <= j Then
temp = arr(i)
arr(i) = arr(j)
arr(j) = temp
i = i + 1
j = j - 1
End If
Wend
Call QuickSort(arr, first, j)
Call QuickSort(arr, i, last)
End Sub

Note that this function compares the text case-sensitively (“day 1” doesn’t match “Day 1”), unlike the worksheet formulas. It returns #VALUE! if a matching row has text in the data range, and the workbook needs to be saved as a macro-enabled file (.xlsm).

MEDIANIF with the FILTER Function

If you use Microsoft 365 or Excel 2021, the easiest way is to combine MEDIAN with FILTER. FILTER returns only the values from B1:B12 where column A has “Day 1”, and MEDIAN calculates the median of those values. Just press Enter.

=MEDIAN(FILTER(B1:B12,A1:A12="Day 1"))

MEDIANIF without Ctrl + Shift + Enter in Older Versions

In Excel 2010 to 2019, you can also use AGGREGATE, which works without Ctrl + Shift + Enter. Function number 16 is PERCENTILE.INC, and the 50th percentile (0.5) is the same as the median. Option 6 tells it to ignore the errors that the division creates for the rows that don’t match:

=AGGREGATE(16,6,B1:B12/(A1:A12="Day 1"),0.5)

MEDIANIF with Multiple Criteria

To test more than one condition, multiply the conditions inside IF. The following formula calculates the median for the rows where column A has “Day 1” and column C has “East” (use Ctrl + Shift + Enter in Excel 2019 and older):

=MEDIAN(IF((A1:A12="Day 1")*(C1:C12="East"),B1:B12))

Multiplying the conditions works like AND: a row is included only when both are TRUE. To use a condition from a cell instead of typing it, replace “Day 1” with the cell reference, like A1:A12=E1.

Blank Cells in the Data Range

If a matching row has a blank cell in B1:B12, the IF part returns 0 for it, and that 0 changes the median. To leave out the blank cells, add a second condition that checks that the value isn’t empty:

=MEDIAN(IF((A1:A12="Day 1")*(B1:B12<>""),B1:B12))

Frequently Asked Questions

Is there a MEDIANIFS function in Excel?

No. Excel has AVERAGEIFS, MAXIFS and MINIFS, but no MEDIANIF or MEDIANIFS. You need MEDIAN with IF or FILTER, as shown above.

Why does my MEDIAN IF formula return a wrong value?

In Excel 2019 and older, the formula wasn’t entered with Ctrl + Shift + Enter, so it doesn’t work on the whole range. Check for curly braces around the formula in the formula bar. Blank cells in the data range can also pull the median down, as they count as 0.

Can I use OR logic in MEDIAN IF?

Yes. Add the conditions instead of multiplying them, and check that the sum is greater than 0. This formula takes the rows with “Day 1” or “Day 2”:

=MEDIAN(IF(((A1:A12="Day 1")+(A1:A12="Day 2"))>0,B1:B12))
Puneet Gogia

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel

Leave a Comment