Forum Discussion
Multiple records, same user, evaluate from and to dates to define a status
=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.
=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.
thank you so much! The formula works perfectly, and I appreciate the reminder about "ctrl+shift+enter".