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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!