Need help with an 'If then, or' formula

JAAH

=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.

JAAH

Thank you both very much for all of your assistance

Paul Newcome

Happy to help. 👍️

Kaveri Vipat

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!

Paul Newcome

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

Thank you both very much for all of your assistance

Paul Newcome

Happy to help. 👍️

JAAH

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

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

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

Paul Newcome

Happy to help. 👍