You want to count the number of duplicate Opportunities on an Account so you can either clean up unneeded Opportunities, or find out what’s happening in the sales process to cause duplicates to happen.
You will need to create two rollup settings, two number fields, plus a formula field.
Create two number fields on Account. One called “Total Number of Opportunities” and one called “Number of Unique Opportunities.”
The first rollup will be to get the Total Number of Opportunities.
The second rollup will be to get the Number of Unique Opportunities. It determines the uniqueness of the Opportunity based on its Name, but if necessary more complex criteria could be added.
The third field used in this use case is a formula field that returns a number called “Number of Duplicate Opportunities” on Account. The formula for this field is as follows:
Total_Number_of_Opportunities__c - Number_of_Unique_Opportunities__c
By subtracting the Total Number of Opportunities from the Number of Unique Opportunities, we are able to determine the Number of Opportunities that are duplicates.