Need help with a formula

Question

I need some help with this formula. Using two tables, the formula field (PC Assigned ((User field)) ) lives in the customers table.
Lead - Region is multiple choice field
EC is a user reference field
Here's the logic:

If the (Lead - Region) = x then result a
if the (Lead - Region) = y then result b
If the (Lead - Region) = z AND the (EC) = specific user, then c

The last case is because we have a region that is split with two people.

I've tried a few different ways to write this and am getting errors in different spots no matter what way I try. Any insight is helpful!

------------------------------
Darian Vigil
------------------------------

ahuvabrown · Answer

Can you copy/paste your exact formula?------------------------------Ahuva Brown------------------------------

katlyncowley · Answer

I know it seems silly, but put AND in lowercase - and. I've had formulas not work properly when and is in all caps.&nbsp;IF( [Lead - Region] = x, a,&nbsp; &nbsp; &nbsp; [Lead - Region] = y, b,&nbsp; &nbsp; &nbsp; [Lead - Region] = z and [EC] = user, c,&nbsp; &nbsp; &nbsp; [Lead - Region] = z and [EC] = otherUser, d,&nbsp; &nbsp; &nbsp; e // ** optional result if none of those are true ***&nbsp;)------------------------------Katlyn Allen kallen@eatatjacks.com------------------------------

darianvigil · Answer

If(Contains([Lead - Sales Regional manager], "Idaho", Jamie@essolar.com)),
If(Contains([Lead - Sales Regional manager], "Cache", and {Lead-EC], zach@essolar.com, linda@essolar.com))

It's throwing me errors after "Idaho" it's saying the "," is "Expecting )"

------------------------------
Darian Vigil
------------------------------

stevenshweky · Answer

try thisIF(Contains([Lead - Sales Regional manager], "Idaho"), "Jamie@essolar.com",Contains([Lead - Sales Regional manager], "Cache") and [Lead-EC]=true, "zach@essolar.com",&nbsp;"linda@essolar.com")Can you tell me what field type [Lead-EC] is?&nbsp; If it is not a checkbox field then please explain in words what logic you are looking for involving that field.------------------------------Steven Shweky------------------------------

katlyncowley · Answer

The {Lead] part will also throw a syntax error. The reason you're getting the "Expecting )" error is because you didn't close out the Contains condition. You can also delete the multiple IFs and only use one. If condition, then result, then other condition, then other result, else something different.&nbsp;Like this:&nbsp;If(Contains([Lead - Sales Regional manager], "Idaho"),&nbsp;Jamie@essolar.com,Contains([Lead - Sales Regional manager], "Cache") and [Lead-EC] = ToUser(zach@essolar.com),&nbsp;linda@essolar.com)------------------------------Katlyn Allen kallen@eatatjacks.com------------------------------

Forum Discussion

Need help with a formula

6 Replies