+ Start a Discussion
Angela PetrignanoAngela Petrignano 

Would really appreciate help with a "Simple" Formula

Hi, I'm having some issues putting together a formula that will sum the total for values inserted in the Potentialsales__c field across a number of records (only the values that are listed in salesprob__c of "70%+" should be included in the calculation) 

Potentialsales__c is a currency field, salesprob__c is a picklist with multiple values

to explain myself better; say 5 records currently have entries for Potentialsales__c and salesprob__c as follows;

Account A: Potentialsales__c: $100,000 / salesprob__c: 40%
Account B: Potentialsales__c: $110,000 / salesprob__c: 70%+
Account C: Potentialsales__c: $120,000 / salesprob__c: 30%
Account D: Potentialsales__c: $130,000 / salesprob__c: 10%
Account E: Potentialsales__c: $140,000 / salesprob__c: 70%+
Account F: Potentialsales__c: $150,000 / salesprob__c: 10%
Account G: Potentialsales__c: $160,000 / salesprob__c: 60%
Account H: Potentialsales__c: $170,000 / salesprob__c: 10%
Account I: Potentialsales__c: $180,000 / salesprob__c: 70%+
Account J: Potentialsales__c: $190,000 / salesprob__c: 30%

Ideally the formula should result pull in and sum only the values from accounts B, E and I and therefore display $430,000

Any help on this would be fantastic!
can you clear this: 
Potentialsales__c and salesprob__c are on Account or on another object.
If the field is on another Object then what is the relationship between Account and that object (Master Detail/ Lookup)
Mudasir WaniMudasir Wani

Hi Angela,

As per your records I can understand that the fields belongs to Account only.
Hence only solution is to create a trigger on account .
Also create a new field on account which will have the sum of the records.
In below code I assume it to be totalPotentialsalesSum__c
trigger tgrOnAccount on Account(after insert,after update){
	//Flag to avoid recursion
	public static updateFlag = true;
	Decimal totalCount = 0;
	List<Account> accList = new List<Account>();
	for(Account acc : Trigger.New){
		if(acc.salesprob__c => 70){
			totalCount = totalCount + acc.Potentialsales__c;
//Here you need another for loop to just update the field whcih contains SUM say it is totalPotentialsalesSum__c
	for(Account acc : Trigger.New){
		acc.totalPotentialsalesSum__c = totalCount;
		//avoid recursion 
		updateFlag = false;
		update accList;

Please mark this as solution if this solves your problem, So that if anyone has this issue this can help.