Description
The SUM function calculates the sum of all non-blank values in a subform's field or in two or more fields.
Usage
SUM(VALUE1, VALUE2, VALUE3, ...)
SUM(SUBFORM_FIELD.FIELD_NAME)
Remarks
When used with subform fields, the argument to SUM() must include a reference to the form's subform field and then the field within the subform, using the dot notation.
The SUM function only accepts numeric or boolean arguments.
For the purpose of averaging logical values, TRUE values are treated as 1.0 and FALSE values are treated as 0.0.
Examples
Calculating a total project budget from subrecords
If you have a Projects form, with a subform containing the cities where the project is being implemented, and each city has its own budget, you might want to sum all of the individual budget lines to find the project's total budget using a formula like:
SUM(CITIES.BUDGET)
If you have a project with the following subrecords:
| City | Country | Budget |
|---|---|---|
| Kinshasa | DRC | 10,000 |
| Goma | DRC | 5,000 |
| Gisenyi | Rwanda | 5,000 |
Then the result of SUM(CITIES.BUDGET) would be 20,000.
Summing multiple fields in the same form together
You can also use SUM to add together multiple fields. For example, if you have an activity report form with four quantity fields: WOMEN, MEN, GIRLS, BOYS and you wanted the total number of people reached, you could write:
SUM(WOMEN, MEN, GIRLS, BOYS)
The formula above has the same result as using the + operator:
WOMEN + MEN + GIRLS + BOYS