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
Zach AckermanZach Ackerman 

Sum multiple fields

I wanted to create a formula to sum fields. Right now this is the formula I have. 
(IF(Opportunity.UH_Covered_Lives__c:SUM> 0,Opportunity.Final_Sold_Lives__c:SUM,+IF(Opportunity.UH_Covered_Lives__c:SUM<0,Opportunity.UH_Covered_Lives__c:SUM,0)))

I am attempting to SUM Final Sold Lives (FSL) in the event UH Covered Lives (UCL) is empty and then sum that number to the instances where UCL is not empty. Right now I'm only returning the total for FSL. The below example I would expect the total to be 31 not 11. 





User-added image
Alain CabonAlain Cabon
Hello Zack;

Nice drawing. Baquiat would have liked it.

You have written this: 

IF (Opportunity.UH_Covered_Lives__c:SUM > 0 THEN  
       Opportunity.Final_Sold_Lives__c:SUM
ELSE IF(Opportunity.UH_Covered_Lives__c:SUM < 0 THEN
            Opportunity.UH_Covered_Lives__c:SUM
          ELSE 0 

The following formula is logical but that could not be your need:

  IF ( Opportunity.UH_Covered_Lives__c:SUM > 0,Opportunity.Final_Sold_Lives__c:SUM, 0  )
  + IF ( Opportunity.UH_Covered_Lives__c:SUM > 0,Opportunity.UH_Covered_Lives__c:SUM, 0 )

Alain
Zach AckermanZach Ackerman
Alain, thanks, but it seems in the event that both fields are populated it is suming those both. Example. 

FSL=30 UCL=29 The total is 59 where it should 29
Zach AckermanZach Ackerman
Alain, 

I think the below is a better example. 

Group  1 UCL= 29 FSL=30
Group 2 UCL = 0 FSL = 30
Group 3 UCL = 50 FSL 2


In this example I would want the following to happen. 

If UCL is greater than 0 use UCL 
Else IF FSL is greater than 0 use FSL. Using this logic with the above i would take the following from each group. 

Group 1 = 29
Group 2 = 30
Group 3 = 50
Total = 109


Currently the formula is returning the following
Group1=59
Group2=30
Group3=52
Total=141
 
Alain CabonAlain Cabon
If UCL is greater than 0 use UCL 
Else IF FSL is greater than 0 use FSL. Using this logic with the above i would take the following from each group. 

IF(Opportunity.UH_Covered_Lives__c:SUM > 0, Opportunity.UH_Covered_Lives__c:SUM,
IF(Opportunity.Final_Sold_Lives__c:SUM > 0,Opportunity.Final_Sold_Lives__c:SUM,0))

You have solved yourself your problem writting the formula with IF and ELSE.

IF ( condition , <return value #1  if true> , < return value #2 if false>  / <include new if condition > ) 

Alain
Zach AckermanZach Ackerman
Yes, logically that works within excel, but it isn't working with the salesforce report formula. In salesforce it only counts the FSL. 
Alain CabonAlain Cabon
We will have to see your report. The values are grouped by a field. What field did you choose for grouping the data into your summary report?