Using both a Median and If condition is including blank cells as zero. How to fix?

[RobbieJ2412]

Hello,

I am trying to find the median of a set of numbers if they equal a certain condition. When I do this the median includes the blank cells as zero and is pulling my median numbers lower than they should be
Is there a way to ignore these in this formula or an alternative i could use?

Here is the formula I am using

=MEDIAN(IF(AE3:AE22="a",AS3:AS22))

Thanks

 

[Fluff]

Hi & welcome to MrExcel.
How about

=MEDIAN(IF((AE3:AE22="a")*(AS3:AS22<>0),AS3:AS22))

 

[RobbieJ2412]

Hi & welcome to MrExcel.
How about

=MEDIAN(IF((AE3:AE22="a")*(AS3:AS22<>0),AS3:AS22))

Great thanks that has worked

I am also looking to use something similar for COUNTA

I want to count the number of cells in AS3:AS22 which are not blank but also satisfy the condition (AE3AE22="a")
Is there a way to do so?

Thanks

 

[Fluff]

You can use the countifs function for that. If you need help with it, you will need to start a new thread.