I have a formula field that adds 12 different fields for one specific Opportunity layout on one specific Record Type. I want this same formula field to add 6 different fields in a different Opportunity layout on a different Record Type.

The reason for this functionality would be so I could still pull one report with this one field and the report would include both Record Types.

Please let me know if there is any other information you need me to provide.


Generally speaking, yes, you can, assuming it does not overflow the 5,000 character compiled limit. For example:

  Field1+Field2+Field3+Field4 +Field5 +Field6+

Where parameter one is the value to compare (record type name), parameters 2 through 5 are basically "if value is X, return this value", and the 6th parameter is a default value, in this case, null. There's other ways to do this as well, it just depends on the specific use case. For example, you could use IF instead of CASE. I simply prefer CASE in most cases because it can be extended to many more value comparisons without the complicated structure of nested IF statements.

  • Are you suggesting this formula to be used in the Edit Opportunity Custom Field - Formula Options - Advanced Formula? Nov 30 '16 at 21:11
  • 1
    @Skobiwankanobi Yes, that was the idea. Alternatively, you could also write it as a workflow field update if you prefer.
    – sfdcfox
    Nov 30 '16 at 22:22
  • Is it possible that an existing workflow would get in the way of this formula populating the # from Field 1 for RecordTypeA? The problem I'm having is that RecordTypeB is pulling the correct # while when RecordTypeA is selected the quantity automatically goes to NULL. Nov 30 '16 at 22:42
  • 1
    @Skobiwankanobi formula fields are calculated "on-the-fly," so they should always contain the correct calculations. Check the formula's "Treat blanks as" setting, though, and make sure you treat "Blanks as zeroes", which should allow calculations to occur even with blank values in any of the fields.
    – sfdcfox
    Nov 30 '16 at 22:44
  • I figured it out with your help. I had to change $RecordType.Name to $RecordType.DeveloperName in order for the field to pull in for both Record Types. Not sure why that is, but it works now! Dec 1 '16 at 14:19

Just use an IF statement in your formula: if Record Type == whatever, then SUM(relevant fields), otherwise SUM(alternate fields)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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