Help fixing this formula

PeterM

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!

Paul Newcome

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")