Formula to return median values if true

[sparky]

Hi All,

I'm stuck with this issue for a while now.

I have attached the spreadsheet. This is basically what I want to achieve but excel will not allow me to put this formula in,

if($D:$D = "P1", median($F:$F), "0")

I want the formula to check the column D for rows with P1 and if true, return the median of the corresponding rows in column F.

Can some one please help me out?

Thanks in advance.

 

[NBVC]

Try:

=MEDIAN(IF($D$2:$D$10="P1",$F$2:$F$10))

confirmed with CTRL+SHIFT+ENTER not just ENTER.

Don't use whole column ranges with this formula.

 

[sparky]

Thanks NBVC for your prompt reply. The formula is working fine in the attached spreadsheet. But I'm trying to copy it over to a number of other spreadsheets and I'm getting a #N/A error. Can you please tell me how to resolve a #N/A error for the median formula?

Upon evaluating the formula, I figured that the If statement works fine, but when the median formula tries to search the array ($F$2:$F$10), this returns #N/A. All the ranges I'm searching have values in them, there are no blank cells. (but this formula must treat blank cells as 0)

How can I achieve this?

 

[NBVC]

Not sure I follow. You say F2:F10 all have numbers, but you are still getting #N/A? Is the D210 matching the P1? Is the formatting of the numbers numbers or text? Can you attach a sample with expected results?