Need help with an 'If then, or' formula
=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
-
Thank you both very much for all of your assistance
-
Answers
-
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⬆️"
-
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"))
-
Thank you both very much for all of your assistance
-
-
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 "))))))
-
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"))
-
Thank you @Paul Newcome especially for the extra assistance and clarity!
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.6K Get Help
- 435 Global Discussions
- 152 Industry Talk
- 495 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 508 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- Good day Smartsheet Team, Getting an unparseable error on this formula: =IF($Name@row <> "",(SUMIFS({Expense}, {Period},1, {Type}, OR(@cell = "RES602782", @cell = "RES602497")),"") Trying to pull in a…
- I am working in a separate sheet for calculating metrics. I want to count the number of items in a referenced date range in another sheet that meet certain year criteria. For example, I want to find t…
- I have a column that is a contact list where a single or multiple staff members can be selected to be scheduled. I'm trying to create a formula that counts how many times each individual's names are l…