Excel doesn't provide MEDIANIF and MODEIF. You will have to use Array formulas to achieve these functionality. Let's assume that our data is like below -

1

To calculate MEDIANIF and MODEIF, enter below formulasĀ i.e. not by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

=MEDIAN(IF(A2:A13="M",B2:B13))

=MODE(IF(A2:A13="M",B2:B13))

Non-Array alternatives

For MEDIANIF

=AGGREGATE(16,6,(B1:B13)/(A1:A13="m"),50%)

For MODEIF

=INDEX(B1:B20,MATCH(MAX(INDEX((COUNTIF(B1:B20,B1:B20)*(A1:A20="m")),,)),INDEX((COUNTIF(B1:B20,B1:B20)*(A1:A20="m")),,),0))