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

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?

-

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