# Median with criteria

✭✭✭

Hi,

I am trying to figure out the median number of days a site is in construction IF it is a Tier I, II, or III.

I am referencing another sheet. Range 6 is referencing a column that has each site's construction duration. Range 7 is the column the lists the sites tier level.

=MEDIAN({REFRESH - MASTER TRACKING SHEET Range 6}, IF({REFRESH - MASTER TRACKING SHEET Range 7}, "Tier I"))

I've tried a handful of variations, with and without the IF statement, but I've had no luck.

I appreciate any and all help!

«1

• Overachievers

@aecross try using a collect formula

=median(collect({REFRESH - MASTER TRACKING SHEET Range 6}, {REFRESH - MASTER TRACKING SHEET Range 7}, "Tier I"))

• ✭✭✭

Hi Samuel,

I tried that but am still receiving an #INCORRECT ARGUMENT SET error.

• ✭✭✭✭✭✭
edited 02/16/23

Hi @aecross ,

You can use the COLLECT function in combination with the MEDIAN here:

=MEDIAN(COLLECT({REFRESH - MASTER TRACKING SHEET Range 6},{REFRESH - MASTER TRACKING SHEET Range 7},"Tier I")

If you're using the Tiers in another column, you can substitute the "Tier I" for the [Column ID]@row instead.

Hope this helps; if you've any questions etc. then just ask!

• Overachievers

When you chose the ranges you chose the entire columns correct, and both ranges are on the same sheet?

• ✭✭✭

@Samuel Mueller, yes, that is correct.

• Overachievers

@aecross My best guess would be to double check your cross sheet references and make sure you selected the entire column for both. Incorrect argument set sometimes means you wrote your formula wrong (parenthesis in wrong place maybe) or your range lengths do not match. I've had cross sheet references reset before I hit save and I have to go back and update them.

In my test the syntax for the formula is correct in theory written as

=median(collect({REFRESH - MASTER TRACKING SHEET Range 6}, {REFRESH - MASTER TRACKING SHEET Range 7}, "Tier I"))

• ✭✭✭

@Samuel Mueller here is the formula i'm using: =MEDIAN(COLLECT({REFRESH - MASTER TRACKING SHEET Range 6}, {REFRESH - MASTER TRACKING SHEET Range 7}, "Tier I"))

When I am referencing, I am clicking the column name so the entire column is highlighted. Would it be an issue that some of the cells in both columns are blank? Or that Range 6 is a formula (cell formula, not column formula) and Range 7 is not?

• Overachievers

@aecross those things shouldn't matter, what do you get with the formula

=countifs({REFRESH - MASTER TRACKING SHEET Range 7}, "Tier I") ?

• Overachievers

Also try this and let me know what you get:

=sumifs({REFRESH - MASTER TRACKING SHEET Range 6}, {REFRESH - MASTER TRACKING SHEET Range 7}, "Tier I")

• ✭✭✭✭✭✭

Hello @aecross to add to excellent responses from @Samuel Mueller I recommend wrapping your formula in the =IFERROR statement - that always works for me!

Cheers,

Ipshita

Ipshita Mukherjee

• ✭✭✭

=countifs({REFRESH - MASTER TRACKING SHEET Range 7}, "Tier I") - I got "82" for this.

=sumifs({REFRESH - MASTER TRACKING SHEET Range 6}, {REFRESH - MASTER TRACKING SHEET Range 7}, "Tier I") - i got "2692"

• ✭✭✭
edited 02/16/23

I am still receiving #INCORRECT ARGUMENT SET with the IFERROR...

=IFERROR(MEDIAN(COLLECT({REFRESH - MASTER TRACKING SHEET Range 6}, {REFRESH - MASTER TRACKING SHEET Range 8}, "Tier I")))

This is a tricky one, thank you all for your help!

• Overachievers

Well @aecross I am at a loss. the median/collect formula should work. I have the same concept set up in a test sheet with no issues. maybe try wrapping it in an iferror like @Ipshita suggests.

• Overachievers

It would be =IFERROR(MEDIAN(COLLECT({REFRESH - MASTER TRACKING SHEET Range 6}, {REFRESH - MASTER TRACKING SHEET Range 8}, "Tier I")),"")

with the ifferor

• Overachievers

also you switched to range 8 not range 7

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!