The MEDIANIF / MEDIAN IF formula is not natively available in Excel as opposed to familiar functions in Excel such as AVERAGEIF or SUMIF. The MEDIANIF function should provide us, for a population of numbers, with the middle number that splits the population into 2 halves. Additionally it should allow us to apply a filter so that the Median operation is conducted on only a subset of the population i.e. using an IF statement. Below I will provide 2 ways of solving this missing formula.

## Excel MEDIAN IF via Array Formula

The first approach is to use Excel Array Formulas by combining a regular *MEDIAN* function with an *IF *function. The formula we will use is:

{ =MEDIAN( IF( criteria , median_range ) ) }

where:

**criteria**– is a logical condition involving an Excel Range. If true for a value in this Range the relevant cell in the*median_range*will be used to calculate the median**median_range**– the Excel Range of value containing the population we want to filter (apply criteria) to calculate the median

Notice the { } brackets. This is because this is an *Excel Array Formula*.

## MEDIAN IF Array Formula example

Now let us put this to work. Imagine we have a range of positive and negative values. However, we want to calculate the median only for positive values (e.g. sales and returns, but we want only the sales median):

### Enter the formula

We will start by entering the following formula below.

=MEDIAN(IF(A4:A14>0,A4:A14))

What this defines is we only want an array of *A4:A14* cells that are greater than 0. The problem is that the formulas work on arrays not single cells. The IF formula does not normally accept a range it is expecting a single cell instead. Therefore to make this work for an array we need to tell Excel that this is an *Array Formula*…

### Make it an Array Formula

Instead of hitting *Enter* hit these 3 keys `ALT`+`SHIFT`+`ENTER` **to make it an Array Formula.** Now Excel will know that it needs to split this formula in memory for each cell in the provided range and then to merge it back. Otherwise it would show you an error message.

## Excel MEDIAN IF using VBA UDF

In case you want to use the MEDIANIF function directly like you would use an AVERAGEIF function we can resort to a custom VBA UDF Function (UDF = User Defined Function). See how this would look like for the same example below:

Below I will show you how to implement MEDIANIF using VBA UDF functions. I am assuming you already have your Developer ribbon visible. If not see my tutorial here.

- Add a new Module to your VBA Project
- Add the code snippet below to the new module
- Save the file as XLSM

The VBA code snippet can be copied from below:

Public Function MEDIANIF(rng As Range, criteria As String, Optional median_range As Range = Nothing) Dim medRng As Range, tmp As Range Dim firstRow As Long, firstCol As Long firstRow = rng.Cells(1, 1).Row firstCol = rng.Cells(1, 1).Column For Each tmp In rng.Cells If Application.Evaluate(vbNullString & tmp.Value & criteria) Then If median_range Is Nothing Then If medRng Is Nothing Then Set medRng = tmp Else Set medRng = Union(medRng, tmp) End If Else If medRng Is Nothing Then Set medRng = median_range.Cells(tmp.Row - firstRow + 1, tmp.Column - firstCol + 1) Else Set medRng = Union(medRng, median_range.Cells(tmp.Row - firstRow + 1, tmp.Column - firstCol + 1)) End If End If End If Next tmp MEDIANIF = Application.WorksheetFunction.median(medRng) End Function

See the result in the VBA Project:

The code may seem complicated but what it does is use the VBA Evaluate function to run the criteria on each cell of the criteria range (*rng*). If the condition is true then the corresponding cell from the *median_range *will be added to the population for which the median is to be calculated.

Notice that *median_range* is optional. This is because by default the criteria range (*rng* parameter) is assumed to be the median range as well – unless the parameter is provided.

### See parameters for the MEDIANIF UDF

Excel VBA UDF functions by default do not show you those fancy function tooltips. However you can click on the *fx* button marked in red below and a familiar window will show up for you to provide the arguments.

All in all the MEDIANIF User Defined Function will make using it much more seamless than having to figure out the shortcut and logic for Array Formulas. The code above can also be easily adjusted for just about any type of function with conditions i.e. MODEIF etc.

## Excel MEDIAN IF Array Formula vs VBA UDF

So which one is better? As much as I love the elegance of the VBA UDF….from a practical and performance point of view if you are Excel savy it will be more convenient to use the Excel Array Formula version. In case you need to embed a lot of these MEDIAN IF statements performance impact might be visible as well – because VBA User Defined Functions do not allow usage of multiple cores (no multithreading). One additional (slight) difference is that you will need to always save your file as a XLSM (Macro enabled file) otherwise the function logic will be lost.

**What is your take?**