Learn how to use the **MEDIAN IF formula** in Excel to identify the middle number of values that meet one or more specific criteria.

The **median is the middle number in a sorted list of numbers**. We frequently use it because the average is not getting a comprehensive picture of the data set. However, if your data set contains peaks, it is worth preferring the median. You can use the built-in MEDIAN function to calculate the median in Excel. What if you want to use one or more conditions?

You can use a workaround with the combination of standalone MEDIAN and IF functions. Or you can use user-defined functions to create easy-to-readable formulas.

If you want to take a deep dive into Excel formulas, we recommend our **definitive guide**.

## Generic formula to apply MEDIAN IF

The function uses two required arguments and has a simple syntax.

**Syntax:**

`=MEDIANIF(range, criteria)`

**Arguments:**

**range**: the range of cells that contains values**criteria**: the condition that you want to use

In the example, we find the median and use the following condition: “**value > 20.**” Next, select the range B3:B13 and create a named range, “**data**“.

**Formula:**

`=MEDIANIF(data, ">20")`

**Result:**

The formula creates an array that contains numbers greater than 20.

**Explanation:**

Based on **boolean logic**, the formula will use only these values where the condition is **TRUE**.

```
={FALSE, TRUE, TRUE, TRUE, FALSE, TRUE, TRUE, FALSE, TRUE, TRUE, FALSE}
={30, 70, 80, 40, 50, 70}
=MEDIAN({30, 70, 80, 40, 50, 70})
```

## MEDIAN IF – Multiple criteria

In this example, we will show you how to calculate a median of a data set if you have more criteria. The **MEDIANIFS** user-defined function enables you to use multiple range/criteria pairs like the normal IFS function. Unfortunately, Excel does not contain this function; **download the practice file** if you want to use it.

**Syntax:**

`=MEDIANIFS(range, [range1,criteria1], [range2, criteria2]…..)`

We aim to calculate the median using the data set that meets the following conditions.

- criteria1: group = “A”
- criteria2: value > 50

**Formula:**

```
=MEDIANIFS(data,range1,criteria1, range2, criteria2)
=MEDIANIFS(data,group,"=A",data,">50")
```

First, the formula finds and gets matching values where the Group =”A”.

`={10, 40, 20, 80, 90, 70}`

After that, the formula returns an array with values greater than 50. In this case, the return array contains three values:

`={80, 90 70}`

The second criteria filter the values, and the formulas return 80.

### Array formula to calculate MEDIAN IF

The next example will demonstrate calculating the median if you want to use the built-in Excel functions. However, for the sake of simplicity, we will use the above-demonstrated data set.

Good to know when you are working with an array formula, you should use the **Ctrl + Shift + Enter** keys instead of a simple Enter.

**The formula in F3:**

`{=MEDIAN(IF(group=E3,data))}`

Copy the formula down to calculate the median for group B.

### Advantages of user-defined functions

We love **UDF**s, so we recommend you build your function library, like the DataFX add-in. Excel is great, but it has some limitations.

To implement a new function, you have two options: store the code in your Workbook or create a custom add-in that contains a reusable and portable collection.