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.

Kelly Pratt

Solution Consultant

Echo Consulting


Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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?



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!