The Problem
There are 2 accounts listed below, but the Grand Total is 4. How do I summarize the number of unique account records?
Go to App Setup (you’ll need to be an admin) and select Accounts and then Fields:
Click ‘New’ under Account Custom Fields section:
Select Data Type as ‘Formula’ and click Next:
Give your new field a name, select ‘Number’ and then press Next:
Enter the figure ‘1’ in the formula field and click Next:
Unselect the Add to page layout option and hit Save.
Go to “customize” your accounts report. Add your new field (AccNo) as a column. Select the “Summarize this Field” option as shown below:
Select “Sum” and then “Apply”:
Run your report and hey presto, the number of unique account records is shown at the foot of the column:
Tip
I’ve created a custom formula field that displays the Account AccNo field shown above on the Opportunity object.
That allows me to create an Opportunity report, put AccNo as a column, summarize that column and see the true Account record count when I run Opportunity reports. It’s worth mentioning you can use the Power of One on any object.