Forum Discussion

Philip Cassidy's avatar
Philip Cassidy
Copper Contributor
Jun 13, 2018

IFS AND ELSE functions.

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"


 

Excel Desktop
Formulas & Functions
  • IngeborgHawighorst's avatar
    IngeborgHawighorst
    Jun 14, 2018

    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:

     

     

  • IngeborgHawighorst's avatar
    IngeborgHawighorst
    MVP
    Jun 14, 2018

    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:

     

     

Resources