IF and Median function to return median value of all the numbers that meet certain criteria

[olivierv]

Hello,

I'm having a problem with a data file for which I want to return the Median value that meet certain criteria.
Suppose I want to return the median EV/EBITDA value for all companies that have more than 50% of their sales in North America for the year 2009, what is the best way to do this?

 

[Special-K99]

=MEDIAN(IF(A1:A21=2009,IF(B1:B21>50%,G1:G21,0)))
Array formula, use Ctrl-Shift-Enter

 

[olivierv]

=MEDIAN(IF(A1:A21=2009,IF(B1:B21>50%,G1:G21,0)))
Array formula, use Ctrl-Shift-Enter

Many thanks, this works. I tried to built a new function with a nested IF function, but I can't get it right.
I used MEDIAN(IF(A1:A21=2009,IF(AND(B1:B21>50%, C1:C21>10%, D1:D22>50%), G1:G21,0))) but it keeps returning #NUM.
@Special-K99, do you have a solution or can you tell what I'm doing wrong?