MEDIAN IF with multiple arguments

Question

=MEDIAN(IF(Platform!R$2:R$821="Unit (W)";IF(Platform!R$2:R$821="Unit (S)";
  IF(Platform!R$2:R$821="Unit (F)";Platform!AE$2:AE$821))))

Can someone please correct this formula? I want to find the Median of the data in column AE2:AE821, but only for the values with the text specified in the IFs functions. I get #NUM! back, thanks

Answer 1

By putting the consecutive IFs in the TRUE portion it is acting like AND. R cannot be both "Unit (S)" and "Unit (F)" so put them in the FALSE postition:

=MEDIAN(
    IF(Platform!R$2:R$821="Unit (W)",Platform!AE$2:AE$821,
    IF(Platform!R$2:R$821="Unit (S)",Platform!AE$2:AE$821,
    IF(Platform!R$2:R$821="Unit (F)",Platform!AE$2:AE$821))))

Now it will be OR

It can also be shortened using * for AND and + for OR:

=MEDIAN(
    IF((Platform!R$2:R$821="Unit (W)")+
    (Platform!R$2:R$821="Unit (S)")+
    (Platform!R$2:R$821="Unit (F)"), 
     Platform!AE$2:AE$821))

Note in my Excel I use , instead of ;, so you may need to change all the , to ;