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")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!