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

  • Paul Newcome
    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")

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!