# Combining an IF / MIN Formula

Options
✭✭✭✭✭

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.

Tags:

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
edited 12/14/21
Options

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))

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!