Median ifs...what is the best formula?

[PP3321]

Thanking you in advance for your generous support...

What would you suggest is the best formula for doing median ifs?

My current solution is to use nested ifs in Array Formula.
I tried also And() but this is not working.

I am wondering if there are easier ways to do this...

*I use Excel 2010

 

[Hui]

Whats wrong with the formula in F5?
It works well

The formula in F8 is not structured correctly, you can try:
=MEDIAN(IF((B2:B10="B")*(C2:C10="Emily"), D2:D10)) Ctrl+Shift+Enter

 

[PP3321]

＠Hui

1. Thank you! solution in F5 is OK but I have to share this with colleagues who do not know much about Excel.

So I wanted to ask experts like you if there are simpler way...

2. Thank you for the 2nd formula!
I did not know that I could multiply 2 logical tests!!

 

[bines53]

Hello friends,

It is also possible that,

=AGGREGATE(17,6,D2:D10/(B2:B10="B")*(C2:C10="Emily"),2)

David

 

[PP3321]

@bines53
Thank you! I have not thought of using Aggregate function!

 

[Hui]

The logic behind multiply two logicals is to effectively replicate and And function on the two arrays
The result will only be true where they are both true
So:
(B2:B10="B")*(C2:C10="Emily")
can be read as
(B2:B10="B")and(C2:C10="Emily")

that is how ir is read, not an Excel And() function

 

[PP3321]

@Hui
Thank you for explanation!