GROUPSUM
- Juliane Wetzel
Syntax
GROUPSUM(<number>)
Description
Adds its arguments.
Empty or <null> records of a group aren't calculated into GROUPSUM().
Calculating a column with a (or multiple) <error> records always returns an error for its group.
This is an aggregate function.
Example
Given the following data:
| Groups | Participants |
|---|---|
| group1 | 6 |
| group1 | 18 |
| group1 | 5 |
| group1 | 5 |
| group1 | <null> |
| group2 | 45 |
| group2 | 76 |
| group2 | 104 |
| group2 | 16 |
group2 | 3 |
| group3 | 5 |
| group3 | 17 |
| group3 | <error> |
First create a group using GROUPBY(#RawData!Groups)
| Groups |
|---|
| group1 |
| group2 |
| group3 |
Then use the GROUPSUM(#RawData!Participants). The result is the sum of the values of the column in relation to the GROUPBY() column.
| Groups | Participants_SUM |
|---|---|
| group1 | 34 |
| group2 | 244 |
| group3 | <error> |
Additional Information
Replicate the SUMIF Function
The SUMIF function can be accomplished by combining the two separate functions GROUPSUM and IF.
Example
Given the following data:
| Key | Value |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
first create groups that contain the values you want to sum. This example excludes values less than 2 from the total sum.
GROUPSUM( IF( (#Data!Value >= 2); #Data!Value; 0) )
The result of this formula executes the GROUPSUM function with the specified conditional statement. Both the groups had a value that was 1. This value was changed to 0 as specified in the IF statement. The new sum of the groups in this example are displayed in the Total_Value1 column.
| Key | Total_Value | Total_Value1 |
|---|---|---|
| 1 | 3 | 2 |
| 2 | 6 | 5 |