In this article:

Median IF Google Sheets (Easiest Way in 2024)

May 8, 2024

In this article we will show you how to use the IF and MEDIAN functions to create a MEDIAN IF formula. Example formulas and images included.

MEDIAN IF in Google Sheets 

For the purpose of the below demonstration an example sheet was created, access this by clicking here and follow along at home.

In our example below, we will use the MEDIAN IF function with an array formula to determine the median performance score of workers within separate branches of a business.

1. Click to Highlight an Empty Cell 

Click to highlight an empty cell in your sheet. This cell is where the Median IF calculation will be made.

In our example we have selected cell F4, below the header of ‘Median Score’.

How can i use Median if in Google Sheets 

2. Enter the formula: =ARRAYFORMULA(MEDIAN(IF(Cell Ref1:Cell Ref2=Cell Ref3,Cell Ref4:Cell Ref5)))

In an empty cell enter the MEDIAN IF formula in the following format:
=ARRAYFORMULA(MEDIAN(IF(Cell Ref1:Cell Ref2=Cell Ref3,Cell Ref4:Cell Ref5)))

Formula Breakdown: 

 =ARRAYFORMULA: This is a function that allows you to execute multiple calculations across a range of cells. In our example, it will calculate the unique median values for all cell references in column E

(MEDIAN: The Median function measures and calculates the median in a numerical set of values.

(IF: The IF function returns values based on a true or false condition

(Cell Ref1: This is the first cell parameter in the formula. It can be a single cell, entire column or row (example C1 or C)

Cell Ref2: This is the second cell parameter. This can also be a singular cell, entire column or row.

=Cell Ref3: This will be the cell that the previous selections will be compared to and if the value is equal to. In our example this will be cell E4 which contains the Unique formula.

,Cell Ref4: The first of the second set of parameter cells to be compared by the formula. This can be a singular cell, entire column or row.

:Cell Ref5)): The final cell parameter is for comparison by the Median formula.

In the example below, we have used the formula to return a median if value result by entering:


This IF formula states that IF any values in B4:B13 = the value in E4 (Brown), get the corresponding values in C4:C13. 

The MEDIAN function then gets the median of these values and returns a general answer based on the entire selected data range.

The array formula returns a value specific to the unique data in our example table.

Implementing Median if in Google Sheets

3. Press Enter to See the Results 

With the formula ready, press the Enter key. The median value will now display for the first comparison cell in E4.

How to use Median If in Google Sheets

4. Click and Drag the Blue Square to Fill The Formula Down the Column

Click and hold on the small blue square in the corner of the highlighted cell (like the one displayed in the example below in F4) and drag down to copy the formula down the other cells. The cell references in the formula will automatically be updated to reflect the change in cell.

How to use Median If in Google Sheets

The median results for all of your unique values will now be automatically generated like in our example below. 

Using Median if in Google Sheets

If you enjoyed this article, you might also like our article on how to use the UNIQUE IF in Google Sheets or our article on how to use the count function in Google Sheets. 

If you want to learn how to mail merge in Google Sheets, we also suggest checking out our detailed guide. 

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.
Get your copy of our free Google Sheets automation guide!
  • 27 pages of Google Sheets tips and tricks to save time
  • Covers pivot tables and other advanced topics
  • 100% free

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->