Since Google Sheets Query doesn’t contain the Median function, the quickest way to find item-wise median in Google Sheets is using the Pivot table.

The Pivot table in Google Sheets has the Median function and other aggregation functions such as Sum, Min, Max, Average, etc.

It’s a few clicks away to create a Pivot table report in Google Sheets that will portray an item-wise median.

For example, assume I’ve invited bids for two projects in my hand from different contractors. Here are the bid prices that I got in tabular form.

I have received five bids for Project 1 and four for Project 2.

From this data, how to find the median bid prices of the two projects separately?

I would recommend using the Google Sheets Pivot table though we can accomplish the same using native formulas too.

Let me start with the formulas to find the item-wise (here, project-wise) median in Google Sheets.

Item Wise Median Using Drag and Drop Formulas

How to find the median bids for the said two projects in hand?

Of course, using a formula, you can find the item-wise median, which I have explained here – Median Function in Google Sheets [Advanced Tips and Tricks].

For your quick reference, here are the same and different variations of the formulas tuned for our above table.

Step # 1:

In cell D2, enter the below UNIQUE formula to return the unique project names.

=unique(A2:A)

Step # 2:

Then in cell E2, enter the below MEDIAN + FILTER and copy it to cell E3.

=median(filter($B$2:$B,$A$2:$A=D2))

Instead of MEDIAN + FILTER, there is an alternative formula using MEDIAN + IF.

=ArrayFormula(MEDIAN(IF($A$2:$A=D2,$B$2:$B)))

Try this combination in cell F2 and copy it down. That’s all about formulas for the median based on items in a list.

Of course, we can convert these two drag-down formulas to array formulas using the MAP lambda function.

But I am not doing that since our Pivot table is much easy compared to that.

Median Calculation Using Pivot Table in Google Sheets

We can easily create a Pivot table report to show the item-wise median in Google Sheets.

Let me explain the same in a few steps.

Step # 1: Selecting the Source Data and Output Cell.

Select columns A and B (click on A, press and hold the Shift key, and click on B) and go to the menu Insert > Pivot Table.

On the window that pops up, you can see the range as Sheet1!A:B (assume your sample data is in Sheet1).

Where do you want to insert the pivot table?

I am inserting the Pivot table in the same Sheet cell D1. Please follow the screenshot below.

Step # 2: Pivot Table (Editor) Settings.

On the successful completion of Step #1, you will be able to see the Pivot table editor (a sidebar panel) as below.

• Add the field “Project” against Rows and Filters.
• Click the drop-down under Filters > Status and remove the tick mark against “Blanks.” Click OK.
• Finally, add the field “Bid Price” against Values. Under the label “Summarize by,” select the function Median.

Here is the screenshot of the above final steps to get an item-wise Median using the Pivot table in Google Sheets.

That’s all.

Other than formulas, this way, you can get item-wise Median using the Pivot table in Google Sheets.

Pivot Table Resources

1. How to Filter Top 10 Items in Google Sheets Pivot Table.
2. Extract Total and Grand Total Rows From a Pivot Table in Google Sheets.
3. How to Sort Pivot Table Grand Total Columns in Google Sheets.
4. Month Wise Pivot Table Report in Google Sheets Using Date Column.
5. Filter Multiple Values in Pivot Table in Google Sheets.
6. How to Sort Pivot Table Columns in the Custom Order in Google Sheets.
7. Drill Down in Pivot Table in Google Sheets (Date Field).
8. Adding Calculated Field in Pivot Table in Google Sheets.
9. Group Dates in Pivot Table in Google Sheets (Month, Quarter, and Year).