function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
S. TisdaleS. Tisdale 

Help with Custom Summary Formula to total Amount Converted from Sales Stage

I will admit that I'm relatively new to writing formulas for Salesforce but I just can't seem to get my head 'round why this isn't working

 

AMOUNT.CONVERT:SUM:IF(ISPICKVAL( StageName, "05a - Awaiting OEM Activation", "06 - Closed Won"))

 

I am trying to create a custom Summary Formula that will total the "Amount Converted" revenue from Opportunities with a Sales Stage picklist value 05a or 06.

 

When I try to validate the formula I get this:

Error: Field StageName does not exist. Check spelling.
I've tried a few different variants Opportunity.StageName, OpportunityStage, SalesStage, etc...  Whatever I try I always get the error that the spelling is incorrect or the field doesn't exist. 
Any help would be greatly appreciated.
imuino2imuino2

I see one error on that formula, ISPICKVAL only accept two parameters, the field name, and the value (String).

Try using two ISPICKVAL functions joined by the or operator.

 

Ignacio.
S. TisdaleS. Tisdale

Thanks Ignacio.  Just as a test I removed the additional field label and I'm still getting the error:

 

Error: Field StageName does not exist. Check spelling.

 

Since StageName is a standard field I'm not sure why I'm getting this error.  That said, knowing that I'll need to use the OR operator will save me a lot of headache once I am able to sort out the issue with the StageName error.

 

Thanks Again.
apex whistlerapex whistler

 

Custom summary formula in reports can only utilize numeric based field. It is not possible to use non-numeric fields, such as StageName, in the formula.

 

If you want a report that shows the amount for those specfic stages, then you can try:

1. Create a custom currency formula field on opportunity that holds the amount if the stage matches, such as

IF(ISPICKVAL( StageName, "05a - Awaiting OEM Activation") ||  ISPICKVAL( StageName, "06 - Closed Won"), Amount, 0)

 In the report, select that custom currency formual field (converted) to total.

 

2. Use the report's criteria to set  Stage = 05a - Awaiting OEM Activation, 06 - Closed Won.

 

#1 gives you ability to compare the amounts of those two stages against amounts for other stages.