Skip to main content
Topics#Reports & DashboardsRobin Root's Question
Robin Root (Sencha) asked in #Reports & Dashboards
Feb 21, 2016, 11:56 PM

How can I fix my formula to select territory owner?

Hi-I have two SDRs who are splitting territories. I want to have a custom field to use for reporting. Here is the formula I wrote, but it throws a syntax error ")".  I can't see where the issue is. Any help out there please? THANKS!

 

IF(ISBLANK(State/Province), "None",

 

IF(CONTAINS("AK:AZ:CA:HI:ID:NV:MT:OR:UT:WA:AB:BC:NT:YT:MB:NU:SK:ON", State/Province), "Rachel",

 

IF(CONTAINS("AL:AR:CO:IA:KS:KY:LA:MN:MS:MO:NE:NM:ND:OK:SD:TN:TX:WY", State/Province), "Rachel",

 

IF(CONTAINS("CT:DE:DC:FL:GA:ME:MD:MA:NH:NJ:NY:NC:PA:RI:SC:VT", State/Province), "Wendy",

 

IF(CONTAINS("VA:WV:IL:IN:MI:OH:WI:NB:NL:NS:PE:QC", State/Province), "Wendy",

 

)))))
4 answers
  1. Sunil Sarilla (M&G Investments)
    Feb 22, 2016, 12:46 AM
    Hi Robin,

     

    Two things,

     

    1. You dont have the False Value for your Last If Statement

    IF(CONTAINS("VA:WV:IL:IN:MI:OH:WI:NB:NL:NS:PE:QC", State/Province), "Wendy","None")

     

    2. Since you are using the two digit code, you will need to use the State/Province Code instead of just State/Province and the field API for State/Province Code is StateCode

     

     Please modify the formula to below

    IF(ISBLANK(TEXT(StateCode) ), "None",
    IF(CONTAINS("AK:AZ:CA:HI:ID:NV:MT:OR:UT:WA:AB:BC:NT:YT:MB:NU:SK:ON", TEXT(StateCode) ), "Rachel",
    IF(CONTAINS("AL:AR:CO:IA:KS:KY:LA:MN:MS:MO:NE:NM:ND:OK:SD:TN:TX:WY", TEXT(StateCode) ), "Rachel",
    IF(CONTAINS("CT:DE:DC:FL:GA:ME:MD:MA:NH:NJ:NY:NC:PA:RI:SC:VT", TEXT(StateCode) ), "Wendy",
    IF(CONTAINS("VA:WV:IL:IN:MI:OH:WI:NB:NL:NS:PE:QC", TEXT(StateCode) ), "Wendy","None"
    )))))

     you can further optimize the formula as below

    IF(ISBLANK(TEXT(StateCode)), "None",
    IF(CONTAINS("AK:AZ:CA:HI:ID:NV:MT:OR:UT:WA:AB:BC:NT:YT:MB:NU:SK:ON:AL:AR:CO:IA:KS:KY:LA:MN:MS:MO:NE:NM:ND:OK:SD:TN:TX:WY", TEXT(StateCode) ), "Rachel",
    IF(CONTAINS("CT:DE:DC:FL:GA:ME:MD:MA:NH:NJ:NY:NC:PA:RI:SC:VT:VA:WV:IL:IN:MI:OH:WI:NB:NL:NS:PE:QC", TEXT(StateCode) ), "Wendy","None"
    )))

     

     
Write an answer...
0/9000