MedianIf multiple range and criteria

[C.]

Hi all,

I have this formula that works perfectly for one criteria for the
range A3:A5000:

=MEDIAN(IF((Calc!$C$3:Calc!$C$5000=1)*(Calc!$A$3:Calc!$A$5000=A19)*
(Calc!$E$3:Calc!$E$5000>0),Calc!$E$3:Calc!$E$5000))

I need to modify this formula in order to scan the range A3:A5000 and
if any values in this range are equal to A17 and A18 and A19, I need
it to return the median of the range E3:E5000>0 AND that also
satisfies the condition C3:C5000=1.

Is this possible? Any thoughts? Does this make sense?

Thanks!

 

[T. Valko]

If I understood what you want...

Array entered:

=MEDIAN(IF(ISNUMBER(MATCH(Calc!$A$3:$A$5000,A17:A19,0)),IF(Calc!$C$3:$C$5000=1,IF(Calc!$E$3:$E$5000>0,Calc!$E$3:$E$5000))))

 

[C.]

If I understood what you want...

Array entered:

=MEDIAN(IF(ISNUMBER(MATCH(Calc!$A$3:$A$5000,A17:A19,0)),IF(Calc!$C$3:$C$5000=1,IF(Calc!$E$3:$E$5000>0,Calc!$E$3:$E$5000))))

Excellent! That was perfect. Thank you so much!

One last question - how could I do it if it the range wasn't
sequential. That is, instead of A17:A19, I need it to be A11 and A5?

Thanks again!

 

[T. Valko]

Try this...

Array enterd:

=MEDIAN(IF((Calc!$A$3:$A$5000=A5)+(Calc!$A$3:$A$5000=A11)>0,IF(Calc!$C$3:$C$5000=1,IF(Calc!$E$3:$E$5000>0,Calc!$E$3:$E$5000))))

--
Biff
Microsoft Excel MVP

If I understood what you want...

Array entered:

=MEDIAN(IF(ISNUMBER(MATCH(Calc!$A$3:$A$5000,A17:A19,0)),IF(Calc!$C$3:$C$5000=1,IF(Calc!$E$3:$E$5000>0,Calc!$E$3:$E$5000))))

Excellent! That was perfect. Thank you so much!

One last question - how could I do it if it the range wasn't
sequential. That is, instead of A17:A19, I need it to be A11 and A5?

Thanks again!

 

[C.]

Try this...

Array enterd:

=MEDIAN(IF((Calc!$A$3:$A$5000=A5)+(Calc!$A$3:$A$5000=A11)>0,IF(Calc!$C$3:$C$5000=1,IF(Calc!$E$3:$E$5000>0,Calc!$E$3:$E$5000))))

--
Biff
Microsoft Excel MVP






Excellent! That was perfect. Thank you so much!

One last question - how could I do it if it the range wasn't
sequential. That is, instead of A17:A19, I need it to be A11 and A5?

Thanks again!

Thank you! That worked perfectly!

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Try this...

Array enterd:

=MEDIAN(IF((Calc!$A$3:$A$5000=A5)+(Calc!$A$3:$A$5000=A11)>0,IF(Calc!$C$3:$C$5000=1,IF(Calc!$E$3:$E$5000>0,Calc!$E$3:$E$5000))))

--
Biff
Microsoft Excel MVP






Excellent! That was perfect. Thank you so much!

One last question - how could I do it if it the range wasn't
sequential. That is, instead of A17:A19, I need it to be A11 and A5?

Thanks again!

Thank you! That worked perfectly!