Median with multiple condition formula

[kogur]

Hey folks,

As the title says, I am trying to make this formula work. Any idea what I might be doing wrong?

{=MEDIAN(IF('Raw Median Incidents'!$Y:$Y="1 - Critical",IF('Raw Median Incidents'!$F:$F="2019",IF('Raw Median Incidents'!$G:$G="2/2019",'Raw Median Incidents'!$I:$I))))}

Column Y=Priority
Column F=Year Closed
Column F=Month Closed
Column I = Hours to resolve

I'm sure you all see what I'm doing but just in case - I want the Median Hours to resolve based on priority, year, and month.

It keeps coming back with a NUM error.

I've been on the fence with kutools for years - would that help with this?

 

[kogur]

@Fluff - Any idea why you're formula might be returning a CALC error?

 

[Fluff]

Why avoid using entire columns?

It slows down the workbook, as the formula has to look-at & calculate almost 4.2 million cells. It's better to limit the ranges like

=MEDIAN(FILTER($I$2:$I$10000,($Y$2:$Y$10000="1 - Critical")*($F$2:$F$10000=2019)*($G$2:$G$10000="2/2019")))

and just set the 10,000 to more rows than you will ever need, without going too far

Any idea why you're formula might be returning a CALC error?

There is nothing that matches all 3 criteria.