Need help with an 'If then, or' formula

Options

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


I want it to essentially perform the calculation ([Amount ($/€)]@row - [Credit Amount]@row) only if it's applicable to certain criteria for 6 options.


Any assistance is welcomed.

Best Answers

Answers

  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭
    Options

    Hi @JAAH

    You can try this formula in Rebill Amount column

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


    I hope this helps!

    Thanks,

    Kaveri Vipat

    Senior Associate - Smartsheet Development, Ignatiuz Software

    2023 Core Product Certified

    Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Give this a try:

    =IF([Type of Credit]@row = "Rebill-Invoice Error”, 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, "Not Applicable"))

  • JAAH
    JAAH ✭✭✭
    Answer ✓
    Options

    Thank you both very much for all of your assistance

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options
  • JAAH
    JAAH ✭✭✭
    edited 07/24/23
    Options

    Hi,

    I have a follow-up question. @Kaveri Vipat and @Paul Newcome

    I ended up using the following formula. It does work; however, in looking over it again, I need the formula to read If Invoice 1 status dropdown at the row is 'credit memo required' AND the 'Type of Credit' at the row IS 'Rebill-Invoice Error' then return a value for each status 1-6. Where the status does Not say 'Rebill-Invoice error, I need it to say 'Not applicable'.

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    If you are going with that particular formula, then you will need to change every OR to an AND.


    The other option to save yourself a bit of typing is to use the formula I suggested which cuts out all of the AND functions, only has one OR function, and saves you from having to type out "[Type of Credit]@row = "Rebill-Invoice Error”, " multiple times.

    =IF([Type of Credit]@row = "Rebill-Invoice Error", 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, "Not Applicable"))

  • JAAH
    JAAH ✭✭✭
    Options

    Thank you @Paul Newcome especially for the extra assistance and clarity!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!