How can I create a formula that sums the total number of open opportunities? - Answers - Salesforce Trailblazer Community
Ask Search:
Ananda FortuneAnanda Fortune 

How can I create a formula that sums the total number of open opportunities?

I want to create a formula field to calculate the number of all open opportunities ( all opportunities except closed) to use in a report that then will have a summary formula column that uses this field opportunity. 

Can anyone tell me how to write this formula?

Thank you! 
Best Answer chosen by Ananda Fortune
Steve MolisSteve Molis
I do this type of thing a lot, so on my org I created custom Formula fields that count if an Opportunity is Open

The Formula field is

Field Name = Open 
Datatype = Formula
Result = Checkbox
Formula = 
IsClosed = FALSE



 

All Answers

Sunil SarillaSunil Sarilla
Hi Ananda,
Could you not just filter the Report by Open Opportunities.
If you need to include the closed Opportunities but only need to show the count of open opportunities, then you can create a custom summary formula field and use the following formula
RowCount - CLOSED:SUM
Could you please expand on how you are planning to use the count of open opportunities in the custom summary formula?
 
Ananda FortuneAnanda Fortune
Hi Sunil,

I am not pulling in any closed opportunities in the report and and with custom summary formula column I want to show a percentage of those open opportunities that are showing a particular picklist value out of the total number of my open opportunities. To be able to show the percent of opportunities that have the picklist value, I need a field that will show the total number of open opportunities.
I then want to show this percentage in a dashboard conponent in table form once I have create it in the report.

Thank you for your help.
Steve MolisSteve Molis
I do this type of thing a lot, so on my org I created custom Formula fields that count if an Opportunity is Open

The Formula field is

Field Name = Open 
Datatype = Formula
Result = Checkbox
Formula = 
IsClosed = FALSE



 
This was selected as the best answer
Sunil SarillaSunil Sarilla
In all probability you will need a formula field on the object.
say you have a picklist field with 3 values say p1,p2,p3 and p1 is the particular picklist value.
do you want to show the occurence of p1 across total open opportunities?
say you have 10 opportunities and p1 is selected for 3 records, p2 for 2 records and p3 for 5 records,
do you want to show 30%?
 
Steve MolisSteve Molis
Could you post a screenshot of the Report, Report Type, and any Groupings, Settings, etc, that you're currently using and the results you're getting, and create a mock-up of what you would like your Report results to look like?  (for questions related to Reports and Dashboards those are really helpful)
Ananda FortuneAnanda Fortune
Hi Steve,

Thank you for the formula! I figured it out once I made that field.