+ Start a Discussion
Matthew LohMatthew Loh 

Multiple IF AND picklist conditions for a formula

Hi all. 

Firstly Merry Christmas! :)

My org does basically all of our sales from Partners, and each partner has a different pricing strategy. Within each partner there are also 2 types of services, so really for one partner we are talking about 2 different values which must be calculated. (E.g. Partner 1 AND Service 1, Partner 1 AND Service 2, Partner 2 AND Service 1, etc...)


Both Partner__c and Service__c are restricted picklists (hence why I used TEXT(picklist_field__c) = "blablabla" below.

The expected revenue for each partner would always be based upon 2 custom currency fields, its

I'd like to be able to generate forecasts/dashboard based on expected revenue of each opportunity.

After finding a similar post and using the best-selected answer here https://success.salesforce.com/answers?id=90630000000h1saAAA

I have come up with:
IF(AND(TEXT(Partner__c) = "Partner 1",TEXT(Service__c) = "Service 1",  Annual_turnover__c  * 0.1,
IF(AND(TEXT(Partner__c) = "Partner 2",TEXT(Service__c) = "Service 2",  Annual_turnover__c  * 0.2,
0))
but I keep getting Syntax errors (e.g. extra ")", and when I remove that it becomes extra ",", etc. etc.)

I'm using a simplified version of the expression that needs to be calculated because it's actually going to be something like annual_turnover__c * 0.2 + annual_transactions * 0.1 

But that's simple to implement once I get the actual formula right with the IF AND conditions.

Any ideas on what I'm doing wrong? This is doing my head in on Christmas eve haha :)

I considered using CASE for this formula, but I read in that post I linked above that you can't use Nested IF statements within a CASE Function.

Thanks guys and Merry Christmas!!! :)
 
Best Answer chosen by Matthew Loh
Alain CabonAlain Cabon
Hello,

You just forgot to close your AND conditions (after "Service 1" and 2).

IF(
    AND ( TEXT(Partner__c) = "Partner 1" , TEXT(Service__c) = "Service 1" ),  Annual_turnover__c  * 0.1 ,

    IF(
        AND ( TEXT(Partner__c) = "Partner 2" , TEXT(Service__c) = "Service 2" ),  Annual_turnover__c  * 0.2 , 0
    )
)

Merry Christmas.

Alain

All Answers

Alain CabonAlain Cabon
Hello,

You just forgot to close your AND conditions (after "Service 1" and 2).

IF(
    AND ( TEXT(Partner__c) = "Partner 1" , TEXT(Service__c) = "Service 1" ),  Annual_turnover__c  * 0.1 ,

    IF(
        AND ( TEXT(Partner__c) = "Partner 2" , TEXT(Service__c) = "Service 2" ),  Annual_turnover__c  * 0.2 , 0
    )
)

Merry Christmas.

Alain
This was selected as the best answer
Matthew LohMatthew Loh
@Alain 

Thank you so much!!!!!!! 

You've made my day :)

Merry Christmas!!!

Now I can start working on the more challenging stuff like average opportunity stage close time haha