EXCLUDE ZERO FROM SUMIFS AND MEDIAN IF

[thedeadzeds]

Hi Guys,

Please see below, hope you can help.

Thanks

Is there a way to exclude ZEROS in column V
=MEDIAN(IF('Relevant Employees'!B:B="Male",'Relevant Employees'!V:V))

Is there a way to exclude ZEROS in column U
=SUMIFS('Relevant Employees'!U:U, 'Relevant Employees'!B:B, "Male", 'Relevant Employees'!AB:AB,"Y")/B8

 

[JvdV]

I'd say using FILTER is a good option here. Something along the lines of:

=MEDIAN(FILTER('Relevant Employees'!V:V,('Relevant Employees'!V:V<>0)*('Relevant Employees'!B:B="Male")))

And:

=SUM(FILTER('Relevant Employees'!U:U,('Relevant Employees'!AB:AB="Y")*('Relevant Employees'!B:B="Male")*('Relevant Employees'!U:U<>0)))/B8

You may be able to rework this to exclude whole column from the calculation based on how your data looks (which I don't know at the moment)

 

[RoryA]

Why would you need to exclude 0 from a sum calculation?

 

[thedeadzeds]

I'd say using FILTER is a good option here. Something along the lines of:

=MEDIAN(FILTER('Relevant Employees'!V:V,('Relevant Employees'!V:V<>0)*('Relevant Employees'!B:B="Male")))

And:

=SUM(FILTER('Relevant Employees'!U:U,('Relevant Employees'!AB:AB="Y")*('Relevant Employees'!B:B="Male")*('Relevant Employees'!U:U<>0)))/B8

You may be able to rework this to exclude whole column from the calculation based on how your data looks (which I don't know at the moment)

Thanks very much