Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

    ✭✭✭
    in Formulas and Functions

    =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:
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Answers

    • ✭✭✭✭✭✭

      Hey @JAAH

      Can you send some screeshots?

      Itai Perez

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

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

      · Share on FacebookShare on Twitter
    • Community Champion

      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))))))

      · Share on FacebookShare on Twitter
    • ✭✭✭

      @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'.

      Image.png


      · Share on FacebookShare on Twitter
    • Community Champion

      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:

      image.png


      · Share on FacebookShare on Twitter
    • ✭✭✭

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

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions