Check status series of dates combined IF with true and multiple false

[TwinJohnson]

There are four possible outcomes
If cell A2 contains no date the expected status is "No Entry Sent"
If cell A2 contains a date <= today then the expected status is "Entry Sent"
If cell A2 contains a date <=today and cell B2 contains a date <=today then the expected status is "Released Not Delivered"
If cell A2 contains a date <=today and cell B2 contains a date <=today and cell C2 contains a date <=today then the expected status is "Delivered", if C2 is blank then "Released not Delivered"

The formula should check all three columns that are in the order in which data is expected to flow as illustrated.

I've tried so many different formulas starting off with writing them separately and piecing them together and run into I can use multiple if's with multiple true's but only one false.

Thank you!

 

[TwinJohnson]

Adding column/row in the screenshot

 

[Saba Sabaratnam]

You could use the following formula in D2 and copy it down

=LET(count,COUNTIFS(A2:C2,"<="&TODAY()),IFS(count=0,"No Entry Sent",count=1,"Entry Sent",count=2,"Released / not Delivered",count=3,"Delivered"))


=

Kind regards

Saba

 

[Fluff]

Another option

=CHOOSE(COUNTIFS(A2:C2,"<="&TODAY())+1,"No Entry Sent","Entry Sent","Released / not Delivered","Delivered")