In group header, how to subtract the sum of values in details section from a single value in the group header.

[former_member289643]

I'm creating a report related to land acquisitions, where a single acquisition may consist of multiple pieces of property.

The report uses two tables. One lists the total acres of each acquisition, and the other lists the individual properties (and their acres) of each acquisition.

I have grouped the report by acquisition number, and the details section lists the properties that belong to each group. For each acquisition I want to subtract the sum of the individual acreages from the single value that exists for total acres.

Example:

ACQ1 consists of 5 properties. It totals 20 acres, and the acreage of each of its 5 individual properties is 4 acres.

The group header includes 1) the acquisition number, 2) its total acreage value (20 acres), and 3) a sum of the acres for the 5 properties (4+4+4+4+4=20). What I want to do is subtract that sum (for that specific acquisition) from the total acres value.

My goal is to identify acquisitions that have a discrepancy between those two values. In this specific example there wouldn't be a discrepancy, but in other cases there will be. But I haven't found a way to have the sum only include the 5 properties of that specific acquisition, instead of the sum of all of the acquisitions in the report.

Ideas?

[abhilash_kumar · Accepted solution]

Hi Jon,

Create a formula with this code and place it on the Group Header:

{Total Acerage Field} - Sum({Acerage Field}, {Acquisition Number Field})

-Abhilash

[former_member289643 · Accepted solution]

Thanks Abhilash. I hoped/expected you would chime in, and as usual you gave me the right response; that formula solved my problem.