using an and() formula with an if statement in an array formula in excel

Question

OK so I have a list of client returns in a spreadsheet. Each client also has a risk category assigned to the (risky or safe) and the partner assigned to them (P1 or P2).

Here is the an example of layout

  A           B          C            D
Client  |  Return  |  RiskCat  |  Partner
_________________________________________
John       1.5%       risky       P2
Bill       1.8%       risky       P1
Tim        1.2%       safe        P2
Bob        1.4%       risky       P1
Kate       2.1%       risky       P2
Fred       0.8%       safe        P1
...etc

I have written a "medianif()" function which calculates the median of returns in a given criteria (e.g. the risk category) So for example if I wanted to find the median of returns in the risky category I would do:

=median(if(C:C="risky",B:B,""))

And press ctrl-shift-enter to enter it as an array formula in the cell.

Likewise to find the median of returns for those who have P1 as the partner I would do:

=median(if(D:D="P1",B:B,""))

Both those formulae above work for me but say I wanted to find the median of returns for the risky category AND the P1 partner. Well, I try:

=median(if(and(C:C="risky",D:D="P1"),B:B,""))

Unfortunately this doesn't work and I get a #value error. Is is possible to use AND() in this way and if not how would I achieve what I've after?

Answer 1

No, you can't use AND or OR in this way because those functions return a single result rather than an array. You need to use * instead of AND....or multiple IFs, e.g. either

=MEDIAN(IF((C:C="risky")*(D:D="P1"),B:B))

or

=MEDIAN(IF(C:C="risky",IF(D:D="P1",B:B)))

the latter is possibly marginally more efficient