Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
Matthew 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 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

Alain 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 Loh
@Alain

Thank you so much!!!!!!!

Merry Christmas!!!

Now I can start working on the more challenging stuff like average opportunity stage close time haha
Marina Parascandolo
I am trying to reuse the formula for my use case and I am failing..
I need 3 different output based on Account tiers and Metric tiers:
- if Account Tier contains Premier and Metric Tier contains Premier -> OK
- if Account Tier contains Premier and Metric Tier contains Advanced or Standard  -> NOT OK
- if Account Tier contains Advanced or Standard and Metric Tier contains Premier -> SUPER

Unfortunately SUPER applies to Account tiers with same values as Metric tiers. how can I solve this?

This is the fomula I used:

IF (CONTAINS(FK_Account.Tier_Formula__c, "Premier"),
IF ( AND (CPP_Metric__c.Tier__c = "Premier"),
IF ( OR ( CPP_Metric__c.Tier__c = "Standard",