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

JAAH

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

Itai Perez

Hey @JAAH

Can you send some screeshots?

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

@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

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

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