IFS AND ELSE functions.

[Philip Cassidy]

Hello, I am creating an invoice report sheet and am looking at using IFS AND formula(s) to update a status column based on dates provided in multiple cells. looking for help with creating a single formula based on the below (if possible)...

Criteria:

• If $G6:$I6 are blank, then $J6 = "Invoice Not Generated"
• If $G6 is any date (mm/dd/yyyy) AND $H6:$I6 are blank, then $J6 = "Generated"
• If $G6:$H6 are any dates (mm/dd/yyyy) AND $I6 is blank, then $J6 = "Delivered - Pending Payment" BUT if today >30 days from $H6, then $J6 = "E"
• If $G6:$I6 are any dates (mm/dd/yyyy), then "Paid"

[#2 · Accepted solution]

Hello,

try this for size. 

=IF(COUNT(G7:I7)=0,"Invoice not generated",
IF(COUNT(G7:I7)=3,"Paid",
IF(AND(ISNUMBER(G7),COUNT(H7:I7)=0),"Generated",
IF(COUNT(G7:H7)=2,IF(TODAY()-H7>30,"E","Delivered - Pending payment")))))

Adding line breaks in the formula with Alt+Enter helps keep the formula readable. Here is a screenshot of the formula at work:

[#3]

This is perfect! Thank you!