Multiple records, same user, evaluate from and to dates to define a status

[sf_adam]

I was hoping for help with a formula...

1. Across all records with a given UserID…
2. First determine if there are any records with a missing Sent Date (I have replaced all blank cells with the "`" (backtick/ Grave accent) character)
3. If so, the "status" value for all records for that user should be "Missing Sent Date"
4. If there are no missing Sent Dates, then check all of the Received Dates for that user to see if any are missing
5. If one or more records has a missing received Date, then the "status" value for all records for that user should be "Record Still Open"
6. If there are no missing Sent or Received Dates across all records for the the user…
7. Then check display the Max Received date (this will become the data that the record is closed)

Here's a sample of the data

I assume I would use a combination of "if", "filter", and "Max" formulas, but I'm unsure where to go from there.

Thank you.

Adam

[#2 · Accepted solution]

@sf_adam 

=IF(COUNTIFS($A$2:$A$18,A2,$D$2:$D$18,"`")>0,"Missing sent date",IF(COUNTIFS($A$2:$A$18,A2,$E$2:$E$18,"`")>0,"Record still open",MAX(IF($A$2:$A$18=A2,$E$2:$E$18))))

This formula works in my sample file. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.

[#3]

@OliverScheurich,

thank you so much! The formula works perfectly, and I appreciate the reminder about "ctrl+shift+enter".