PICKLISTCOUNT Function in Salesforce | Count Selected Values in Multi-select field in Salesforce

This Salesforce tutorial will explain the PICKLISTCOUNT Function. We will also learn its syntax and use cases. In addition, we will explore practical business examples of the function in both Salesforce Lightning and Salesforce Classic.

While working as a Salesforce administrator, I recently had the task of counting the number of opportunities in each stage of the sales pipeline to track sales performance.

To fulfill this requirement, I decided to create the formula field where I can use the PICKLISTCOUNT function and count the multi-select picklist selected values.

What is Salesforce PICKLISTCOUNT Function

The Salesforce PICKLISTCOUNT Function is one of the various TEXT functions in Salesforce. It performs text operations on Salesforce fields and returns the number of selected values in a multi-select picklist.

As we all know, in a multi-select picklist,, we can choose multiple values from the list of defined options. So, when the developer needs to evaluate and determine selected values within this field and count selected items, we will use the PICKLISTCOUNT function.

In addition, we can use this function in various cases in Salesforce such as reporting, workflow automation, formula fields, or validation rules. Furthermore, the syntax of the PICKLISTCOUNT function is simple and easy to grasp and given as below:

PICKLISTCOUNT(multiselect_picklist_field)

Here, the multiselect_picklist_field parameter specifies the name of the multi-select picklist field for which we want to count the selected values.

Moreover, we have to take care of one more thing the formula always results in a Number data type and is incompatible with other data types such as Time, Date, Text, Checkbox, etc.

If you pass any other data type rather than a multi-select picklist to the PICKLISTCOUNT function such as text, number, date, date/time, etc. you will get an error “Incorrect parameter type for function ‘PICKLISTCOUNT()’.”

Let’s see a small example of the PICKLISTCOUNT() function to better understand the concept:

Suppose you have a multi-select picklist field named “Electronic Gadgets You Have.” This picklist contains the following options: Phones, Laptops, Cameras, Tablets, Headphones, iPads, Smart Watches, and Personal Computers.

Let’s assume that out of all the values, you have selected the following: “Phones,” “Laptops,” “Headphones,” and “Smart Watch” from this multi-select picklist.

So, if you use the PICKLISTCOUNT() function, it returns you the count of the selected values as 4 in your course, because you have chosen the four options from the multi-select picklist.

See also  LEFT Function in Salesforce

With this, we have learned about the Salesforce PICKLISTCOUNT function, its syntax, and one example of the function to better understand it. Next, we will move ahead and learn how to use the PICKLISTCOUNT function in the formula field in both Salesforce Lightning and Classic with examples.

Read LPAD Function in Salesforce

How to use PICKLISTCOUNT Function in Salesforce Lightning

The following are the steps to use the Salesforce PICKLISTCOUNT Function to determine the number of selected values in a multi-select picklist field.

Step 1: Click on the Gare Icon. -> Setup. Click on it.

PICKLISTCOUNT Function in Salesforce

Step 2: Click on the Object Manager.

PICKLISTCOUNT Function in Salesforce Example

Step 3: Select an Object on which you want to apply the PICKLISTCOUNT function.

Here, I have selected a Product Object.

PICKLISTCOUNT Function in Salesforce Lightning

Step-4: Click on the Field & Relationships -> New” button to create a new field where you use the PICKLISTCOUNT function in a formula field.

PICKLISTCOUNT Function in Salesforce Lightning Example

Step 5: Select the formula field and click on the radio button next to the “Formula” field type. Then, to proceed to the next step, click on the “Next” button.

Salesforce PICKLISTCOUNT Function

Step-6: Now, enter the “Field Label” and “Field Name” and choose “Formula Return Type.” In my case, I entered the field label as “Total Price,” and when I clicked, the field name was automatically populated.

In this case, you have to choose “Currency” as the formula return type by clicking on the radio button next to the currency field type. I keep the “Decimal Place” value the same as by default; if you want to change it, you can. Then, to move to the next step, click on the “Next” button.

Salesforce PICKLISTCOUNT Function Example

Step-7: In this step, you need to enter the formula according to your requirements. First, navigate to the right side of the page, where you see the “Functions” section. From here, click on the dropdown labeled “All Functions Categories.

From this dropdown, select the “Text” category because you know that the PICKLISTCOUNT function falls under the text category of Salesforce functions. On selecting the text, you will get the list of Salesforce functions that are categorized under text functions.

Now, search for the “PICKLISTCOUNT” function, select it, and click on the “Insert Selected Function” button. After the function is inserted into the advanced formula box, you can add parameters according to your specific requirements.

Let’s imagine an example: Suppose you are working in a customizing gifts company where customers can customize their gift by selecting multiple options like color, size, and material. On the basis of the customization total product price is calculated.

See also  Salesforce REGEX Function

For selecting the customizations you have various multi-select picklist fields, so to calculate the total amount on the number of customizations selected, you decided to use the PICKLISTCOUNT function.

According to the scenario, the formula is as given below:

Basic_Charges__c + (PICKLISTCOUNT(Color__c) * 50) + (PICKLISTCOUNT(Size__c) * 100) + (PICKLISTCOUNT(Material__c) * 300)

Here, is a detailed explanation of the formula:

  • First, define the basic cost or service charge of the product.
  • Then, use the PICKLISTCOUNT function that counts the number of colors selected by the customer. After that, we multiply it by the additional cost of 50 based on the color selected.
  • Next, use the PICKLISTCOUNT function that calculates the additional cost based on the number of sizes selected by the customer, and per size we multiply the size by 100.
  • After that, use the PICKLISTCOUNT function that calculates the additional cost based on the number of selected materials to be used in customizing the product, and as per each material take an additional amount of 300.
  • Last, we use the + operator to add all the results of the parts and get the total price accordingly.

Step-8: After entering the formula in the subtab, click on the “Check Syntax” button to ensure that the formula is error-free. If there are no errors, you will receive a message like “No syntax errors in merge fields or function”. But if there are any errors, an error message appears showing you the issue.

Even, you can enter information in the “Description” and “Help Text” fields, and configure how the field should handle empty values. After that, click on the “Next” button and move to the next step.

Salesforce Lightning PICKLISTCOUNT Function

Step-9: In this step, you have to set up the “Field Level Security”. Here, you will need to select the profiles for which you want to grant edit access to this field.

Then, click on the “Next” button to move to the next step.

Salesforce Lightning PICKLISTCOUNT Function Example

Step-10: Here, we need to select which Page Layout we want to display this field.

Otherwise, click the “Save” button to complete creating a formula field in Salesforce where you can use the PICKLISTCOUNT function.

Count Selected Values in Multi select field in Salesforce Lightning

Step-11: Once the formula field is created, you can use the formula field. Let’s see an example:

  • Open the “Products” item, create a new product with the “Basic Charges”, “Color”, “Size”, and “Material” and save the product.
  • Once you save, move to the product details page. Here you will observe the “Total Price” field that shows you the result of the PICKLISTCOUNT function.
See also  FIND() Function in Salesforce

Let’s deeply understand it with the example:

Suppose you have created the product with the basic charge of “$200,” the color selected as “Red” or “Grey,” the size selected as “7.5” x 11.5″, and the material selected as “Leather,” and saved it. Then, the formula executes and gives the total price as “$700.” Hence, the formula effectively counts the multi-select picklist selected values and calculates the total price.

Working:

  • Basic Charges: $200
  • Color: PICKLISTCOUNT(Color__c) * 50: As two color are selected the PICKLISTCOUNT function returns the result as 2 and then multiply it by 50 and give the final result as 100.
  • Size: PICKLISTCOUNT(Size__c) * 100: As only one size is selected the PICKLISTCOUNT function returns the result as 1 and then multiplies it by 100 and gives the final result as 100.
  • Material: PICKLISTCOUNT(Material__c) * 300: As only one material is selected the PICKLISTCOUNT function returns the result as 1 and then multiplies it by 300 and gives the final result as 300.
  • Total Price: Basic Charges + Color + Size+ Material = 200 +100+ 100+ 300 = $700
Count Selected Values in Multi select field in Salesforce Lightning Example

We have learned how to use the PICKLISTCOUNT function in Salesforce Lightning.

Conclusion

In a nutshell, we have learned the Salesforce PICKLISTCOUNT function, which is used to count selected values in the multi-select field in Salesforce. We have also learned the syntax and practical examples that show how the function is implemented.

Moreover, we have explored the step-by-step implementation of how to count the selected values in the picklist multi-select field in Salesforce Lightning and Salesforce Classic with examples.

You may like to read the following articles: