Can I get a syntax check of the following Credit Amount Formula?

=IF((OR([Invoice 1 Status (Dropdown)]@row = "Credit Memo Required" AND ([TYPE of Credit] NOT(Rebill-Invoice Error))), =[Invoice 1 Amount]@row), (IF(OR([Invoice 2 Status (Dropdown)]@row = "Credit Memo Required" AND ([TYPE of Credit] NOT(Rebill-Invoice Error))), =[Invoice 2 Amount]@row), (IF(OR([Invoice 3 Status (Dropdown)]@row = "Credit Memo Required" AND ([TYPE of Credit] NOT(Rebill-Invoice Error))), =[Invoice 3 Amount]@row), (IF(OR([Invoice 4 Status (Dropdown)]@row = "Credit Memo Required" AND ([TYPE of Credit] NOT(Rebill-Invoice Error))), =[Invoice 4 Amount]@row), (IF(OR([Invoice 5 Status (Dropdown)]@row = "Credit Memo Required" AND ([TYPE of Credit] NOT(Rebill-Invoice Error))), =[Invoice 5 Amount]@row), (IF(OR([Invoice 6 Status (Dropdown)]@row = "Credit Memo Required" AND ([TYPE of Credit] NOT(Rebill-Invoice Error))), =[Invoice 6 Amount]@row)))))))


The Credit amount will essentially be equal to the Invoice amount when the 'Invoice Status dropdown' at the row is equal to Credit memo required AND when the 'Type of Credit'} is NOT ('Rebill-Invoice Error').

Tags:

Answers

  • Itai
    Itai ✭✭✭✭✭✭

    Hey @JAAH

    Can you send some screeshots?

    Itai Perez

    Project Manager | Transformation Department

    Gong cha

    If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

    https://www.linkedin.com/in/itai-perez-740543116/

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    This seems to accomplish what your formula appears to be shooting for. Without seeing your sheet setup, I can't be sure.

    =IF([TYPE of Credit]@cell <> "Rebill-Invoice Error", IF([Invoice 1 Status (Dropdown)]@row = "Credit Memo Required", [Invoice 1 Amount]@row, IF([Invoice 2 Status (Dropdown)]@row = "Credit Memo Required", [Invoice 2 Amount]@row, IF([Invoice 3 Status (Dropdown)]@row = "Credit Memo Required", [Invoice 3 Amount]@row, IF([Invoice 4 Status (Dropdown)]@row = "Credit Memo Required", [Invoice 4 Amount]@row), IF([Invoice 5 Status (Dropdown)]@row = "Credit Memo Required", [Invoice 5 Amount]@row, IF([Invoice 6 Status (Dropdown)]@row = "Credit Memo Required", [Invoice 6 Amount]@row))))))

  • JAAH
    JAAH ✭✭✭

    @Carson Penticuff I have two columns that I am trying to compute formulas for. One column is Credit Amount and the other is Rebill Amount. There are two additional columns with information, 'Amount ($/€)' and Invoicing # Amount.

    A column, 'Credit Amount' would be blank only if the 'Invoice # Dropdown status' column was NOT 'Credit Memo Required'.

    The Rebill Amount would only populate if, two things were true; Invoice # dropdown status column would need to say 'Credit Memo Required' and a third column named 'Type of Credit' status would need to be "Rebill-Invoice Error"

    This is what I had for the Rebill column formula:

    =IF(AND([Invoice 1 Status (Dropdown)]@row = "Credit Memo Required", [Type of Credit]@row = "Rebill-Invoice Error"), [Amount ($/€)]@row - [Credit Amount]@row, IF(AND([Invoice 2 Status (Dropdown)]@row = "Credit Memo Required", [Type of Credit]@row = "Rebill-Invoice Error"), [Amount ($/€)]@row - [Credit Amount]@row, IF(AND([Invoice 3 Status (Dropdown)]@row = "Credit Memo Required", [Type of Credit]@row = "Rebill-Invoice Error"), [Amount ($/€)]@row - [Credit Amount]@row, IF(AND([Invoice 4 Status (Dropdown)]@row = "Credit Memo Required", [Type of Credit]@row = "Rebill-Invoice Error"), [Amount ($/€)]@row - [Credit Amount]@row, IF(AND([Invoice 5 Status (Dropdown)]@row = "Credit Memo Required", [Type of Credit]@row = "Rebill-Invoice Error"), [Amount ($/€)]@row - [Credit Amount]@row, IF(AND([Invoice 6 Status (Dropdown)]@row = "Credit Memo Required", [Type of Credit]@row = "Rebill-Invoice Error"), IF([Type of Credit]@row NOT "Rebill-Invoice Error" = "Not applicable "))))))


    The Rebill column when appropriate based on statues, in theory should be [Amount ($/€)]@row - [Credit Amount]@row. The Credit Amount is copied from the invoicing amount when the 'Invoice # dropdown' status column would say 'Credit Memo Required'.


  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    You can simplify your Rebill formula quite a bit:

    =IF([Type of Credit]@row <> "Rebill-Invoice-Error", "Not applicable ", IF(OR([Invoice 1 Status (Dropdown)]@row = "Credit Memo Required", [Invoice 2 Status (Dropdown)]@row = "Credit Memo Required", [Invoice 3 Status (Dropdown)]@row = "Credit Memo Required", [Invoice 4 Status (Dropdown)]@row = "Credit Memo Required", [Invoice 5 Status (Dropdown)]@row = "Credit Memo Required", [Invoice 6 Status (Dropdown)]@row = "Credit Memo Required"), [Amount ($/€)]@row - [Credit Amount]@row))

    This may make it easier to visualize:


  • JAAH
    JAAH ✭✭✭

    Thank you @Carson Penticuff, I appreciate the view as well!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!