Formula to display one date in a range with 3 criteria


Hello everyone,

Can you please help me with a formula?

I have three columns: 'Start date' , 'Dose', 'Site'.

I would like to consider multiple criteria: 1) the start date is in the future 2) the value in the 'Dose' column is 'drug' and 3) the Site is '01'. Based on these criteria there should be a list of dates, but I would only to display one value, the 5th date.

would you have any suggestion on how to do that?

Thank you!



Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    To pull in the date for the 5th time the site uses the drug, you would use a SMALL/COLLECT combo along the lines of

    =SMALL(COLLECT([Start Date]:[Start Date], Site:Site, @cell = "01", Dose:Does, @cell = "drug"), 5)

    Are you wanting this to go on every single row, or are you just pulling this one date for site 1 and then pulling a single date for site 2, so on and so forth?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!