Median across with multiple columns and criteria

[richtks]

Is this possible? I need to figure the median based on market and construction status.

Below is an example. I need the median of multiple project's, in multiple markets for Days in Construction Start (CXs) to today and Days CXs to On Air (OA) based on the On-Air Status (s) being "P".

in turn, I'll need to do the same for completed project which equal "A" in the On Air S.
***Side note... I already have a pivot where I was averaging the days, but extended projects seems to drive up those numbers in markets that are not as busy as others.***

 

[myall_blues]

Perhaps like this?

You haven't indicated which version of Excel you are using, but if you are using Excel 2019 or earlier then these need to be entered as array formulas (CTRL+SHIFT+ENTER).

Book1
	A														O	P	Q	R	S																																									BH	BI	BJ	BK	BL	BM
1																Count to Today	Count to Projected																																												On air status			Type	Median
2																61	86																																												P			Count to Today	51.5
3																42	60																																												P			Count to Projected	73
4																145	156																																												P
5																34	38																																												P
Sheet2

Cell Formulas
Range	Formula
BM2	BM2	=MEDIAN(IF(BI$2:BI$5="P",P$2:P$5))
BM3	BM3	=MEDIAN(IF(BI$2:BI$5="P",Q$2:Q$5))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[richtks]

Perhaps like this?

You haven't indicated which version of Excel you are using, but if you are using Excel 2019 or earlier then these need to be entered as array formulas (CTRL+SHIFT+ENTER).

Book1
	A														O	P	Q	R	S																																									BH	BI	BJ	BK	BL	BM
1																Count to Today	Count to Projected																																												On air status			Type	Median
2																61	86																																												P			Count to Today	51.5
3																42	60																																												P			Count to Projected	73
4																145	156																																												P
5																34	38																																												P
Sheet2

Cell Formulas
Range	Formula
BM2	BM2	=MEDIAN(IF(BI$2:BI$5="P",P$2:P$5))
BM3	BM3	=MEDIAN(IF(BI$2:BI$5="P",Q$2:Q$5))
Press CTRL+SHIFT+ENTER to enter array formulas.

I am using 365 and have updated my profile.

Thank you for your input. I need to be able to break it down by market (12 in total) if possible.

I expanded on your formula a bit, but come up with a #VALUE! error:

=MEDIAN(IFS([@Market],Details[Market Combined],Details[On-Air S]="P",Details[Current Days Count CXs to Today]))

 

[myall_blues]

Without a sample of your data, and especially since the formula is now in table form, it's impossible to tell. A #VALUE! error generally indicates that a value is missing or text is encountered where a number is expected.
I'd suggest testing your modified formula on a small (5-10 records) subset of your original data before transferring it to a larger table.