Remove Duplicate Child Record Counts within a Report

Why Duplicate Counts Occur in Parent-Child Report Types

When you run a report using a joined report type such as Accounts with Opportunities, Salesforce returns one row for each Child record (Opportunity). If a single Account has five Opportunities, the report will show five rows and a record count of five, even though there is only one Account.
This happens because the report type is driven by the Child object. To get an accurate count of Parent (Account) records, you need a custom Formula field that returns the value 1 for each row, which you can then sum to count unique Parent records.

Real-Life Example

You run an Accounts with Opportunities report to count how many customer accounts have open deals. One account — Acme Corp — has 5 open Opportunities. The report shows a count of 5, but you only want to count Acme Corp as 1 Account. By using the formula field workaround below, you can sum the formula field and get the correct count of 1 Account.

Steps to Create a Custom Formula Field for Parent Record Count

Create a custom Formula field on the Parent object (for example, Account) by following these steps:

1. In Salesforce Setup, navigate to the object manager for the Parent object.
2. Click Fields & Relationships, then click New.
3. Select Formula as the Data Type and click Next.
4. Enter a Field Label such as "Count (Formula)" to distinguish this as a counting field.
5. Select Number as the formula return type.
6. Select 0 from the Decimal Places drop-down menu and click Next.
7. In the formula text box, enter the number 1 (just the digit one, no quotes).
8. Click Check Syntax to validate the formula.
9. Click Next, then Next again, then Save.

Using the Formula Field in a Report

After creating the formula field:

1. Open or create a report using the Parent-Child report type (for example, Accounts with Opportunities).
2. Add the new Formula field to the report columns.
3. Summarize the Formula field using the Sum aggregation.
4. The Sum of the Formula field now shows the correct count of unique Parent (Account) records in the report.