Countif with Median

aecross

Hi there!

I am trying to gather the median for a set of numbers but only if it falls in a certain category. I have a formula used in the column I am pulling the median from, and in instances where I don't have all the data, the cell defaults to 0 (as i am using a division formula) which skews the median.

Example: I want to pull the median from the "Construction start to complete (days)" column, but only count the sites that are in the "Construction" phase.

I've tried incorporating Countif or If into the Median formula but I haven't had any luck.

Any and all help is appreciated!

Leibel S

@aecross

Use a combo of MEDIAN and COLLECT. See below, replace column names as needed

=MEDIAN(COLLECT([Construction start to complete (days)]:[Construction start to complete (days)], Phase:Phase, @cell ="Construction"))

aecross

@Leibel Shuchat Thank you!

I forgot to mention that i am referencing a different sheet, so how would this affect the formula?

Leibel S

Just replace the column names with cross sheet references

aecross

@Leibel Shuchat

Here is my formula, it is still showing up as unparsable.

=MEDIAN(COLLECT({New Stores Tracking Sheet Range 4}:{New Stores Tracking Sheet Range 4}, {New Stores Tracking Sheet Range 7}:{New Stores Tracking Sheet Range 7}, @cell ="Construction"))

Leibel S

@aecross

When using cross sheet references you don't put it in twice with the colon separator:

=MEDIAN(COLLECT({New Stores Tracking Sheet Range 4}, {New Stores Tracking Sheet Range 7}, @cell ="Construction"))

aecross

@Leibel Shuchat thank you!

I am hoping to build onto this formula to show the above median but separate it out into dates in the past from the current day and dates in the future from the current day. Below is the formula I'm trying, but not quite sure what to use in place of the AND function. Any thoughts?

=MEDIAN(COLLECT({New Stores Tracking Sheet Range 3}, {New Stores Tracking Sheet Range 7}, @cell = "Construction"), AND({New Stores Tracking Sheet Range 8}@row < = DATE(2022, 11, 1))

Leibel S

No need for the AND:

=MEDIAN(COLLECT({New Stores Tracking Sheet Range 3}, {New Stores Tracking Sheet Range 7}, @cell = "Construction", {New Stores Tracking Sheet Range 8},@cell<=DATE(2022, 11, 1)))