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!

Francesca

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

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?

All comments

Paul Newcome

Are you able to provide some screenshots for context? Sample data is fine.

Andrée Starå

Hi,

I hope you're well and safe!

Can you share some screenshots? (Please delete/replace any confidential/sensitive information before sharing.) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

francescabi

Hello, thank you for your replies, I have attached a screenshot, it is a simplified version of my spreadsheet but hopefully it helps. I would like the formula to display the date highlighted in yellow (then I could apply the same formula for the other sites). Thank you very much

Paul Newcome

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?

francescabi

Hi Paul,

THANK YOU SO MUCH for your input, this is working!!

I am pulling this one date for site 1, then pulling a single date for site 2, etc.

Thank you again, have a good day!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.