Combining an IF / MIN Formula

I am using a COUNT IF / MIN formula to pull in the next upcoming date based on a specific name within a range, but the formula I have is not working. Here is the situation:

Sheet 1: A sheet for an individual client's open cases.

Sheet 2: A sheet that includes milestone dates for ALL clients.

I would like the ability to pull in the next upcoming date from {Sheet 2} based on if it matches the client's name from {Sheet 1}

Here is the formula I'm using in Sheet 1:

=IF(COUNTIFS({Sheet 2}, "Client Name") > 0, MIN(COLLECT({Sheet 2 Date Range}, {Sheet 2 Date Range}, @cell > [Effective Date]@row)))

This formula is pulling in a date, but it's just pulling in the FIRST upcoming date within the sheet, it's not looking to the client when it's further down in the sheet. Hope this makes sense! Would love some help on how to tweak this formula to make it pull in the correct date.

Find more posts tagged with

Formulas

Accepted answers

Kelly Moore

Hey @Kelly Pratt

As written above, the IF statement is only looking if the client name is in your data set. The client's name isn't part of the criteria for the collect. Add the range, client name to the Collect.

Did that work for you?

Kelly

All comments

Kelly Moore

Hey @Kelly Pratt

As written above, the IF statement is only looking if the client name is in your data set. The client's name isn't part of the criteria for the collect. Add the range, client name to the Collect.

Did that work for you?

Kelly

Kelly Pratt

Thank you, Kelly! That worked! Here is my new formula:

=MIN(COLLECT({Sheet 2 Date Range}, {Sheet 2}, [Client Name]@row, {Sheet 2 Date Range}, @cell > [Effective Date]@row))

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.