When it comes to formulas, which are used in a variety of places in Salesforce, the first thing that comes to mind is probably the good old formula field! In case you haven’t yet created your first formula field, feel free to take a look at our interactive tutorial post – learning how to get started may be useful before exploring the examples below.
During this guide, we will dive into a variety of Salesforce formula examples, as well as the common scenarios in which formulas (used in different locations) can make your life as a Salesforce Admin much easier.
Formula Fields 101
Before going through the examples below, it’s worth nothing a few key considerations:
- Formula fields are not editable. If the value displayed is not correct, the formula has to be changed so that the desired outcome will show up on records.
- A formula field can be used for a range of calculations between fields and displaying data from related Object records, as well as displaying pictures.
- After building a formula field, it is automatically populated for historical records as well.
You can check out all the current limitations and restrictions for formula fields here.
Salesforce Formula Examples
We’ve split the examples into several categories based on where they’re being used to cover a few more use cases. You can also categorize them in other ways depending on your preference.
Before starting to build your formulas, make sure you are familiar with at least the main operators and formula functions:
Field-Level Formulas
From the most simple to some of the most complex examples, formula fields are calculated after the record is saved, and can include a wide variety of functions, operators, and fields from the object at hand or related objects.
Calculate Basic Commission on Opportunities
IF(Amount < 10000, Amount * 0.10, Amount * 0.15)
A simple IF statement that checks if the Opportunity Amount is less than 10K – if so, the commission is 10%, and if it is greater, the commission is 15%. This calculates and displays that commission.
Calculate a Lead Score
(IF( AnnualRevenue = 0, 0, 10)) +
(IF(ISPICKVAL(Industry, “”) , 0, 10)) +
(IF( MobilePhone = “”, 0, 10))
A basic Lead score formula that looks at whether fields are blank or not (and assigns them a score if not). We can then add these together. Please note, the ISPICKVAL function is necessary if you are using a picklist field.
Calculate an Account’s Region
Here’s a great example of a formula field assigning West, Central, East, South, North, or Other based on the state. This was taken from the Salesforce Example page.
IF(ISBLANK(BillingState), “None”,
IF(CONTAINS(“AK:AZ:CA:HA:NV:NM:OR:UT:WA”, BillingState),
“West”, IF(CONTAINS(“CO:ID:MT:KS:OK:TX:WY”, BillingState),
“Central”, IF(CONTAINS(“CT:ME:MA:NH:NY:PA:RI:VT”, BillingState), “East”,
IF(CONTAINS(“AL:AR:DC:DE:FL:GA:KY:LA:MD:MS:NC:NJ:SC:TN:VA:WV”,
BillingState), “South”,
IF(CONTAINS(“IL:IN:IA:MI:MN:MO:NE:ND:OH:SD:WI”, BillingState),
“North”, “Other”))))))
Using Images in Formula Fields
One of the best things about Formula Fields is the ability to display images as an output. So, in the example above with the Lead Score, instead of outputting a number, we could output an image to display how “Hot” the lead is.
Salesforce has a bunch of images that can be used straight out of the box. You can grab a whole list of them here (you will need to remove some of the URL, as instructed). There is also an AppExchange pack you can install to get hold of some more.
Continuing the example with Lead score, I’ll now show you a formula with images.
CASE( Lead_Score__c , 10 ,
IMAGE(“/img/samples/light_red.gif”, “light”),
20,IMAGE(“/img/samples/light_yellow.gif”, “light”) ,
30 ,IMAGE(“/img/samples/light_green.gif”,
“light”), “”)
Using a CASE statement (similar to an IF statement but it can have more outcomes), we can look at our Lead Score field and assign an Image URL using the IMAGE function. If the score is 10, assign a red traffic light. If the score is 20, assign a yellow light. And if 30, assign the green. Easy!
Record Owner Category
This is a formula that, once again, can be used in validation rules, automations, and reports – you name it! It can be built as a formula field or directly within the automation, and it will be handy to know at all times whether the record owner for objects that support queue assignment is a queue or user.
IF(BEGINS(OwnerId, "005","User","Queue"))
OR
IF(BEGINS(OwnerId, "00G","Queue","User"))
OR (if you prefer the formula output to be a checkbox)
IF(BEGINS(OwnerId,"005",1,0)
Information from Parent Account
Fairly often, there might be a request to get and display certain information from records above in the Account hierarchy on individual records. The below IF() statement is perfect for a couple of levels higher, but CASE() can also be used, especially when multiple outcomes are possible.
IF(NOT(ISBLANK(Parent.Parent.Id)),Parent.Parent.AnnualRevenue,
IF(NOT(ISBLANK(Parent.Id)),Parent.AnnualRevenue, "")
Check if a Field Value Contains a Certain String
When possible, the ideal scenario is to use exact criteria, be it report filters or formulas, as equals is much more effective and less prone to errors than to check if a string contains a word. Sometimes, however, this does make sense.
For example, the Type field on the Opportunity in my developer sandbox has four values, of which three contain the word “existing”, based on which the Opportunity priority will be defined.
Note that Type is a picklist field, hence the function TEXT() will be used to transform the picklist into text within the formula.
IF(CONTAINS(TEXT(Type),"Existing"),"High", "Medium")
The CONTAINS() function, however, does not support multi-select picklists. In case that is what you’re looking forward to checking, you will have to use INCLUDES() instead, as well as the value the multi-select field should include. For this, I created a custom multi-select picklist for Type, just to be able to include more granular values which can be simultaneously selected.
IF(INCLUDES(Type__c,"Existing Customer"), "High","Medium")
Also, since we’re on the topic of multi-select picklists, there is a function that has been released more recently that you should know – this can be used in a formula, which returns the count of how many values are chosen in a multi-select picklist (although I’m hoping you don’t have too many of them!).
PICKLISTCOUNT(Type__c)
Hyperlink
The transition to Lightning experience means that many of us would simply use a rich text component to display nicely organized hyperlinks at the top of the record page. However, there are still situations where the hyperlink formula is useful, as for once the lightning component can’t be displayed and accessed in reports or list views.
You could add a hyperlink formula field next to the Opportunity Next Step, to provide instructions on how to complete it directly on the detail page (in both Lightning and Classic). Also, you can add a couple links in as well – why not?
Tip: Make sure you use the shortest URL possible.
HYPERLINK("www.google.com", "How to complete all relevant Next Steps") & BR() &
HYPERLINK("www.google.com", "How to Close Deals faster")
Number of Days Between Two Dates
Definitely the simplest date formula in Salesforce is to calculate the number of days between two dates. This can then be used in reports, perhaps as part of automation criteria, or to set deadlines for records to be actioned, and offer users additional visibility.
To find the number of days since a record was created, simply subtract the CreatedDate from TODAY().
TODAY() - DATEVALUE(CreatedDate)
Find Out the Day of the Week
Perhaps the first formula that comes to my mind when thinking about date fields, is the one helping us find out the day of the week. For example, this way you can easily provide insight and analyze which days of the week are the most productive – to help close Opportunities, for example.
CASE(WEEKDAY(CloseDate),
1, "Sunday",
2, "Monday",
3, "Tuesday",
4, "Wednesday",
5, "Thursday",
6, "Friday",
7, "Saturday",
"N/A")
Object-Specific
Looking at Objects in particular? These articles should be your next stop:
Pardot-Specific
We have plenty of resources available when it comes to Pardot (Marketing Cloud Account Engagement), including some interactive tutorials:
You may also be interested in the following articles:
Formulas Within Salesforce Reports
While most of us are already familiar with IF() row-level formulas in a Salesforce report, or a very simple average, it might slip our minds how much easier it is to create a report formula (rather than formula fields), when possible.
Row-Level Formula
A very easy one to use – row-level formulas allow you to define a value of the formula for each of the records in the report. For example, instead of creating a formula field to calculate the number of days between two date fields, you can do so in a report.
The below report only shows Accounts with one Closed Won Opportunity. I was curious to see how many days had passed between the Account being created and the Opportunity being closed. In my use case, whenever an Account is created, an Opportunity should be created as well.
CLOSE_DATE - DATEVALUE(ACCOUNT_CREATED_DATE)
Complex Report Formulas
In Salesforce reports, there are two formulas that, if understood and used correctly, can enable you to build powerful reports using the standard Salesforce reports only.
These formulas can be used at grouping levels, and as they are very specific to the calculation you’re looking forward to running in your report, make sure you read all documentation details, along with the examples.
The one use case of PARENTGROUPVAL() that I always go back to, is the possibility of showcasing the percentage each grouping level represents out of the total. This can prove to be really useful when looking forward to seeing a calculated procentual breakdown, for example, of how the Opportunities forecasted to close this FQ are doing.
RowCount / PARENTGROUPVAL(RowCount, GRAND_SUMMARY)
Salesforce Reports Formula Fields
Check out the following articles for support with reports:
Validation Rules
Activity Validation
One question I have seen popping up a couple times recently relates to creation validation rules on Activities. It is possible, with the mention that the validation should be created on either the Task or Event object. The below example is on the Task object, and is meant to require more details if certain conditions are met:
AND(ISPICKVAL(Priority,"High"),
ISPICKVAL(Status,"Waiting on someone else"),
ISBLANK(Description))
Following the same situation as above, instead of checking if the Comments field is blank, let’s make sure users always input a decent number of characters for the Comments of a High Priority Task. The LEN() function returns the count of the number of characters, helping you ensure the minimum level of detail is there.
AND(ISPICKVAL(Priority,"High"),ISPICKVAL(Status,"Waiting on someone else"),
LEN(Description) < 30)
Checking the Previous Value of a Field
Especially in validation rules, PRIORVALUE comes to the rescue when users that normally have permissions to update certain fields, shouldn’t do that anymore. Common examples vary from editing key Opportunity fields when the Opportunity is closed, to updates on converted Leads. This one can also prevent admins from making unintentional mistakes on data points which should never undergo changes.
AND(IsClosed,PRIORVALUE(OwnerId) <> OwnerId)
More About Validation Rules
You can learn more about how to use validation rules in Salesforce with our step-by-step guide.
Salesforce Flow Formulas
Similar to Process Builder, being able to build formulas directly in the declarative automation tool is something that Salesforce has been doing quite well. The latest addition to the family is the ability to use formulas as an entry criteria for the flow, instead of individually selecting fields and defining filter logic – and, of course, covering more possible scenarios.
Make sure you take a look at the flow formulas considerations before embarking on this journey!
Entry Criteria Formula
There will be situations where you might need more than one of the same type of flow on the same object, and this is where the entry criteria comes into play. These will ensure that, in this case, your flow is not triggered by all changes to the record – the Salesforce recommendation is to always use entry criteria for a flow which should run when records are updated.
Of course, the advantage of using this formula is that not only can you filter by fields on the record, but also on related records. In this case, the flow I built will create a Task for the Opportunity Owner’s manager whenever the Stage is changed to a specific one, and the Annual Revenue of the Account exceeds a given threshold.
AND(ISPICKVAL({!$Record.StageName},"Proposal/PriceQuote"),
{!$Record.Account.AnnualRevenue} > 500000)
You can, however, always opt to use only fields from the triggering record, depending on your use case and business requirement, without having to select each field, operator, and value individually.
AND(ISPICKVAL({!$Record.StageName},"Proposal/PriceQuote"),
{!$Record.Amount} > 70000)
Flow Formula Resources
Perhaps the formula I use most often within flows (when creating records), is IF(). The reason behind this is that it’s far more efficient than having one Decision element, followed by two Create Records elements, depending on the outcome.
In this example, upon every new Opportunity creation, a specific placeholder Close Date is automatically updated depending on the Opportunity Type (which in my org is mandatory at record creation).
IF(ISPICKVAL({!$Record.Type},"New Customer"),
TODAY() + 180, TODAY() + 90)
This formula can now be used to set the field value for Close Date, and it will be dynamic based on the value from the Type field.
Flow Formulas
When it comes to Flow formulas, we have you covered:
Other Formulas
Formulas in Approval Processes
There are endless possibilities with approval processes in Salesforce, which also provide a great way to document the history of approvals on records. Read our Ultimate Guide for more context.
Roll-Up Formulas
Check out the following resources:
Default Value Formula
It is especially useful to add a placeholder into a text field, perhaps with an example of a format – the formula editor helping to define the default value is something I find to be quite underused.
Summary
It is clear that formulas are here to stay. Whether you choose to calculate certain values in a report formula to save you from creating an extra field, or you save the business a few clicks by using a formula field to display data from a related record, day-to-day processes are sure to involve at least one of them.
What do you think of our Salesforce formula examples? And is there a formula that has saved the day for you? Feel free to share your experience in the comments section below.
This Pardot article written by:
Salesforce Ben | The Drip
Lucy Mazalon is the Head Editor & Operations Director at Salesforceben.com, Founder of THE DRIP and Salesforce Marketing Champion 2020.
Original Pardot Article: https://www.salesforceben.com/salesforce-formula-examples/
Find more great Pardot articles at www.salesforceben.com/the-drip/
Pardot Experts Blog
We have categorized all the different Pardot articles by topics.
Pardot Topic Categories
- Account Based Marketing (ABM) (7)
- Business Units (14)
- ChatGPT / AI (3)
- Completion Actions (5)
- Connectors (10)
- Custom Redirects (4)
- Data Cloud (2)
- Demand Generation (8)
- Dynamic Content (6)
- Einstein Features (12)
- Email Delivery (17)
- Email Open Rates (3)
- Pardot A/B Testing (2)
- Email Mailability (16)
- Do Not Email (1)
- Double Opt-in (2)
- Opt Out / Unsubscribe (14)
- Email Preferences Page (6)
- Engagement Studio (16)
- Industries (1)
- Non Profit (1)
- Landing Pages (9)
- Lead Generation (1)
- Lead Management (13)
- Lead Routing (3)
- Lead Scoring (16)
- Leads (3)
- Marketing Analytics – B2BMA (9)
- Marketing Automation (1)
- Marketing Cloud (3)
- Marketing Cloud Account Engagement (4)
- Marketing Cloud Growth (2)
- New Pardot Features (6)
- Opportunities (2)
- Optimization (2)
- Pardot Admin (64)
- Duplicates (1)
- Marketing Ops (1)
- Pardot Alerts (1)
- Pardot API (2)
- Pardot Automations (3)
- Pardot Careers (12)
- Pardot Certifications (4)
- Pardot Consulting (1)
- Pardot Cookies (3)
- Pardot Custom Objects (3)
- Pardot Email Builder (8)
- Pardot Email Templates (9)
- HML (6)
- Pardot Events (16)
- Pardot External Actions (1)
- Pardot External Activities (4)
- Pardot Forms (29)
- Form Handlers (8)
- Pardot Integrations (20)
- Data Cloud (1)
- Slack (1)
- Pardot Lead Grading (5)
- Pardot Lead Source (2)
- Pardot Lightning (1)
- Pardot Migration (1)
- Pardot Nurture / Drip Campaigns (1)
- Pardot Personalization (3)
- Pardot Profiles (1)
- Pardot Releases (18)
- Pardot Sandboxes (2)
- Pardot Segmentation (5)
- Pardot Strategy (7)
- Pardot Sync (2)
- Pardot Sync Errors (1)
- Pardot Tracker Domains (5)
- Pardot Training (3)
- Pardot Vs Other MAPs (4)
- Pardot Website Tracking (2)
- Reporting (22)
- Salesforce and Pardot (31)
- Marketing Data Sharing (2)
- Pardot Users (3)
- Salesforce Automation (5)
- Salesforce Flows (2)
- Salesforce Campaigns (20)
- Salesforce CRM (3)
- Record Types (1)
- Salesforce Engage (3)
- Salesforce Queues (2)
- Security and Privacy (1)
- Tags (3)
- The Authors (504)
- Cheshire Impact (9)
- Greenkey Digital (51)
- Invado Solutions (37)
- Jenna Molby (9)
- Marcloud Consulting (6)
- Nebula Consulting (60)
- Pardot Geeks (44)
- Salesforce Ben | The Drip (235)
- SalesLabX (3)
- Slalom (4)
- Unfettered Marketing (46)
- Uncategorized (1)
- Website Tracking (2)
- Website Search (1)
More Pardot Articles
See all posts
This Pardot article written by:
Salesforce Ben | The Drip
Lucy Mazalon is the Head Editor & Operations Director at Salesforceben.com, Founder of THE DRIP and Salesforce Marketing Champion 2020.
Original Pardot Article: https://www.salesforceben.com/salesforce-formula-examples/
Find more great Pardot articles at www.salesforceben.com/the-drip/