0

I'm trying to write a multiple nested if formula but keep receiving the following error.

Error: Syntax error. Extra ','

IF((TODAY() > CloseDate && TEXT(StageName)='Donation Likely'),TRUE),   
IF((TODAY() > CloseDate && TEXT(StageName)='Donation Prospecting'),TRUE),
IF((TEXT(StageName) <> 'Donation Likely'||TEXT( StageName ) <> 'Donation 
Prospecting'|| TODAY() < CloseDate,FALSE)
1
  • I am trying to say that if the first 2 IF statements evaluates to true then the value of the checkbox should be TRUE. If the last IF statement evaluates to be true then the value of the checkbox should be FALSE. – Victor Mora Jan 25 '17 at 5:11
0

You are missing some parts. Try this and pay attention to the ??? which is the false part of the last if

IF(
   (TODAY() > CloseDate && TEXT(StageName)='Donation Likely'),
   TRUE,   
     IF(
         (TODAY() > CloseDate && TEXT(StageName)='Donation Prospecting'),
         TRUE,
            IF(
             TEXT(StageName) <> 'Donation Likely'||TEXT( StageName ) <> 'Donation 
             Prospecting'|| TODAY() < CloseDate,
             FALSE,
             ????
           )
     )
)

You are basically doing (from what I gather from your attempt):

IF(
   EVAL,
   TRUEPART,
   FALSEPART = IF(EVAL,TRUEPART, FALSEPART = IF(EVAL,TRUEPART,FALSEPART)
)

Every IF statement has an Eval, TruePart, and FalsePart. When nesting you can get lost easily.

Note I did not evaluate to see if there was a better way of doing what you are trying to do so Optimization is still possible and may be posted as an answer by another SFSE team member :0

3
  • The first 2 if evaluations should be true and the last if should be false – Victor Mora Jan 25 '17 at 4:55
  • @VictorMora - no idea what that means. You may be able to take what I have posted and mold it into your needs.... – Eric Jan 25 '17 at 4:56
  • IF((TEXT(StageName) <> 'Donation Likely'||TEXT( StageName ) <> 'Donation Prospecting'|| TODAY() < CloseDate),FALSE,???) Parens were missing in the first clause of the if statement to group them together. – crmprogdev Jan 25 '17 at 4:57
0

Don't you think this formula can be simplified?

In first two IF CloseDate is common only difference is the StageName so, we can combine both conditions as:

IF(
      (
         TODAY() > CloseDate 
         && (TEXT(StageName)='Donation Likely' || 
             TEXT(StageName) = 'Donation Prospecting')
      ), 
      TRUE, 
      FALSE
)

In all other conditions you just want FALSE even the CloseDate > Today() regardless of stage, formula will evaluate to false.

Note: It is little difficult initially but make sure brackets are closed properly.

Not the answer you're looking for? Browse other questions tagged or ask your own question.