Help fixing this formula
Find how much is the Hard cost for each case. Hard cost is equals to Type are “Award”, “Demolition” and CostSubtype are Award (Appraisal Value), Other (Describe), Title/Search/Appraisal/Negotiation and Description contains “Title Policy” plus the amount that has Type is “Void” and the Invoice Numbers are “RFP for closing” or “RFP for closing Jan”
Buyout Hard Costs
Hard Cost:
Test 1- Error is #INVALID OPERATION
=SUMIFS([Amount (+/-)]:[Amount (+/-)], [Type]:[Type], "Award", [Cost Subtype]:[Cost Subtype], {"Award (Appraisal Value)","Other (Describe)","Title/Search/Appraisal/Negotiation"}, [Description]:[Description], "Title Policy") + SUMIFS([Amount (+/-)]:[Amount (+/-)], [Type]:[Type], "Demolition", [Cost Subtype]:[Cost Subtype], {"Award (Appraisal Value)","Other (Describe)","Title/Search/Appraisal/Negotiation"}, [Description]:[Description], "*Title Policy*") + SUMIFS([Amount (+/-)]:[Amount (+/-)], [Type]:[Type], "Void", [Invoice Number]:[Invoice Number], {"RFP for closing","RFP for closing Jan"})
Test 2- Error is #UNPARSEABLE
=SUMIFS([Amount (+/-):Amount (+/-)], [Type]:[Type], CONTAINS("Award", @row), [Cost Subtype]:[Cost Subtype], CONTAINS("Award (Appraisal Value)", @row):CONTAINS("Title/Search/Appraisal/Negotiation", @row), CONTAINS("Title Policy", [Description]:[Description]))+ SUMIFS([Amount (+/-):Amount (+/-)], [Type]:[Type], "Void", [Invoice Number]:[Invoice Number], {"RFP for closing", "RFP for closing Jan"})
Find how much is the soft cost for each case. Soft cost is equals to Type are “Additional Project Compliance Costs”, “Title/Search/Appraisal” and Description contains “Not Title Policy” plus the amount that has Type is “Void” and the Invoice Numbers are “51736652”, “51736654”, “51736981”, “51746172”
Buyout Soft Costs
Test 1- Error is #INVALID OPERATION
=SUMIFS([Amount (+/-)]:[Amount (+/-)], Type:Type, "Additional Project Compliance Costs", Description:Description, "<>Title Policy") + SUMIFS([Amount (+/-)]:[Amount (+/-)], Type:Type, "Title/Search/Appraisal", Description:Description, "<>*Title Policy*") + SUMIFS([Amount (+/-)]:[Amount (+/-)], Type:Type, "Void", [Invoice Number]:[Invoice Number], {"51736652", "51736654", "51736981", "51746172"})
Test 2- Error is #UNPARSEABLE
=SUMIFS([Amount (+/-)]@row:[Amount (+/-)], Type:Type, "Additional Project Compliance Costs", Description:Description, "*Not Title Policy*") + SUMIFS([Amount (+/-)]@row:[Amount (+/-)], Type:Type, "Title/Search/Appraisal", Description:Description, "*Not Title Policy*") - SUMIFS([Amount (+/-)]@row:[Amount (+/-)], Type:Type, "Void", [Invoice Number]:[Invoice Number], {"51736652", "51736654", "51736981", "51746172"})
Test 3- Error is #UNPARSEABLE
=SUMIFS([Amount (+/-):Amount (+/-)], [Type]:[Type], CONTAINS("Additional Project Compliance Costs", @row), [Description]:[Description], CONTAINS("Not Title Policy", @row)) + SUMIFS([Amount (+/-):Amount (+/-)], [Type]:[Type], CONTAINS("Title/Search/Appraisal", @row), [Description]:[Description], CONTAINS("Not Title Policy", @row)) - SUMIFS([Amount (+/-):Amount (+/-)], [Type]:[Type], "Void", [Invoice Number]:[Invoice Number], {"51736652", "51736654", "51736981", "51746172"})
Test 4- Error is #UNPARSEABLE
=SUMIFS([Amount (+/-):Amount (+/-)], [Type]:[Type], CONTAINS("Additional Project Compliance Costs", @row), [Description]:[Description], NOT(CONTAINS("Title Policy", @row)))+ SUMIFS([Amount (+/-):Amount (+/-)], [Type]:[Type], CONTAINS("Title/Search/Appraisal", @row), [Description]:[Description], CONTAINS("Title Policy", @row))- SUMIFS([Amount (+/-):Amount (+/-)], [Type]:[Type], "Void", [Invoice Number]:[Invoice Number], {"51736652", "51736654", "51736981", "51746172"})
Thank you!
Answers
-
You need to incorporate an OR statement.
You have:
=SUMIFS([Amount (+/-)]:[Amount (+/-)], [Type]:[Type], "Award", [Cost Subtype]:[Cost Subtype], {"Award (Appraisal Value)","Other (Describe)","Title/Search/Appraisal/Negotiation"}, [Description]:[Description], "Title Policy")
You need:
=SUMIFS([Amount (+/-)]:[Amount (+/-)], [Type]:[Type], "Award", [Cost Subtype]:[Cost Subtype], OR(@cell = "Award (Appraisal Value)",@cell = "Other (Describe)",@cell = "Title/Search/Appraisal/Negotiation"), [Description]:[Description], "Title Policy")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!