Countif with Median

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!

Tags:

Answers

  • Leibel S
    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
    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
    Leibel S ✭✭✭✭✭✭

    Just replace the column names with cross sheet references

  • aecross
    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
    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
    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
    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)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!