# Collecting earliest date based on certain criteria

Options

Hey Smartsheet,

I'm trying to pull the earliest date that a status symbol was Red or Yellow unless that date is followed by a Green status symbol and then a Red or Yellow status symbol. In that scenario, I would want the Red/Yellow date after the Green date to be shown in the Roll-up Sheet.

In the example below, the Roll-Up Sheet should be showing 11/24 instead of 11/2 but I cannot find out what formula (or if its even possible).

Data Table

Roll-up Sheet

Thank you

Options

That worked, thank you!! One additional question.

For that formula , what statement would I add to have it pull based on the ID in my roll-up sheet?

In the example below the formula you provided would pull 12/2/20 for IT-0341 and 11/20/20 for IT-0342.

I've tried nesting an IF statement "ID@row" into the summary sheet to pull from the source sheet but I cant get the formula to not break.

• ✭✭✭✭✭✭
Options

@Jeffrey Homme Try:

=LARGE(COLLECT(Date26:Date29, Status26:Status29, <>"green"), 1)

where row 26 is top of range, 29 is bottom of range. This basically shows the most recent date for a row that is not green in status. (The formula returns a date so it needs to be in a date column.)

That should meet this scenario. But I am not sure about the IF, THEN wrinkles you imply with the unless statement.

Cheers,

Dale

• Options

Thank you for the response. What I'm trying to do is pull the earliest date the Status was in Red/Yellow unless it was followed by a date in Green. I've tried every COLLECT, MIN/MAX, IFS that I can think of but cant seem to get the results shown below. Below are two examples on what I'm trying to do.

Scenario A = 11/4/20

Scenario B = 11/29/20

Options

That worked, thank you!! One additional question.

For that formula , what statement would I add to have it pull based on the ID in my roll-up sheet?

In the example below the formula you provided would pull 12/2/20 for IT-0341 and 11/20/20 for IT-0342.

I've tried nesting an IF statement "ID@row" into the summary sheet to pull from the source sheet but I cant get the formula to not break.

• Options

Thank you for both formulas, you have helped me so much!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!