Never use SUM and LOOKUP in the same formula. This can lead to extremely long calculation times. For more information, see Formulas and their effects on model performance .
The SUM aggregation function sums values in a result module based on mapping from a source module.
For example, you can use the SUM function to sum revenue across several product categories.
Syntax
Values to sum[SUM: Mapping, SUM: Mapping 2, etc.]
Arguments
| Argument | Data type | Description |
| Values to sum | Number | The values to sum based on the Mapping argument. |
| Mapping | List, date, time period | The mapping that determines which values to sum. Each instance of this argument must be a dimension present in the Values to sum argument. This argument can be repeated to provide multiple mappings. |
The SUM function returns a numeric result. The line item that contains the SUM function must be dimensioned by all dimensions used for the Mapping argument.
Syntax example
'Employee Expenses'.Salary[SUM:'Employee Details'.Region, SUM: 'Employee Details'.Role]
In this example, the values from the Salary line item are summed for each employee role and region.
Additional information
Summary method
SUM can also be used as a summary method. You can use the summary method on line items with the number data type.
Pull data from one module to another
You can use the SUM function to pull data from one module into another. However, the SUM function only works with number values. If you need to do this for other data types, such as Boolean or text, use a different aggregation function.
To determine whether to use SUM or LOOKUP, see Formula usage tips.
Use SUM with the Users list
You can reference the Users list with the SUM function. However, you cannot reference specific users within the Users list as this is production data, which can change and make your formula invalid.
Constraints
The dimensions of the Mapping argument must also be dimensions of the Values to sum argument.
Excel equivalent
Examples
In these examples, there are several modules.
The first is a Transactions Module. In this module, the Transactions list displays on rows. Several line items that contain information about each transaction display on columns:
- Product line item, which has the list data type with the Products list. Displays the product that each transaction relates to.
- Region, which has the list data type with the Organization list. Displays the location of the sale for each transaction.
- Sale value, which contains the numeric value of each transaction.
- Sale date, which contains the date of each transaction.
| Product | Region | Sale value | Sale date | |
| Transaction 01 | Apples | London | $ 10,000.00 | 1/23/2020 |
| Transaction 02 | Bananas | Birmingham | $ 5,000.00 | 2/15/2020 |
| Transaction 03 | Pears | London | $ 6,000.00 | 4/1/2020 |
| Transaction 04 | Pears | Birmingham | $ 4,000.00 | 5/15/2020 |
| Transaction 05 | Apples | Paris | $ 4,500.00 | 5/19/2020 |
| Transaction 06 | Carrots | Munich | $ 7,000.00 | 5/20/2020 |
| Transaction 07 | Lettuce | Berlin | $ 11,000.00 | 5/26/2020 |
| Transaction 08 | Bananas | London | $ 12,000.00 | 5/28/2020 |
| Transaction 09 | Apples | London | $ 6,500.00 | 5/29/2020 |
| Transaction 10 | Cucumbers | Lyon | $ 2,000.00 | 5/30/2020 |
| Transaction 11 | Carrots | Paris | $ 1,000.00 | 3/4/2021 |
| Transaction 12 | Carrots | Berlin | $ 3,500.00 | 3/20/2021 |
| Transaction 13 | Bananas | Birmingham | $ 8,000.00 | 4/1/2021 |
| Transaction 14 | Apples | London | $ 9,500.00 | 6/15/2021 |
| Transaction 15 | Peaches | Munich | $ 5,000.00 | 6/28/2021 |
| Transaction 16 | Pears | Munich | $ 7,000.00 | 7/7/2021 |
| Transaction 17 | Lettuce | Berlin | $ 12,000.00 | 8/11/2021 |
| Transaction 18 | Cucumbers | Lyon | $ 3,000.00 | 9/22/2021 |
| Transaction 19 | Bananas | Paris | $ 1,500.00 | 9/30/2021 |
| Transaction 20 | Apples | London | $ 9,500.00 | 10/1/2021 |
The Transactions Module is used as the source for each of the examples below.
Sum sales by region
This example uses the SUM function to sum values from the Transactions Module for each region.
The formula used for the Total Sales line item is:
Transactions Module.Sale value[SUM:Transactions Module.Region]
| London | Birmingham | UK | Paris | Lyon | France | Munich | Berlin | Germany | Total Company | |
| Total Sales | $ 53,500.00 | $ 17,000.00 | $ 70,500.00 | $ 7,000.00 | $ 5,000.00 | $ 12,000.00 | $ 19,000.00 | $ 26,500.00 | $ 45,500.00 | $ 128,000.00 |
Sum sales by region and product
This example uses the SUM function to sum values for each combination of product and region. The page dimension is line items, which displays the Sales line item. On rows, the Products list displays, and on columns, the Region list displays.
The formula used for the Sales line item is:
Transactions Module.Sale value[SUM:Transactions Module.Product, SUM:Transactions Module.Region]
| London | Birmingham | UK | Paris | Lyon | France | Munich | Berlin | Germany | Total Company | |
| Apples | $ 35,000.00 | 0 | $ 35,500.00 | $ 4,500.00 | 0 | $ 4,500.00 | 0 | 0 | 0 | $ 40,000.00 |
| Peaches | 0 | 0 | 0 | 0 | 0 | 0 | $ 12,000.00 | 0 | 0 | $ 12,000.00 |
| Bananas | $ 12,000.00 | $ 13,000.00 | $25,000.00 | $ 1,500.00 | 0 | $ 1,500.00 | 0 | 0 | 0 | $ 26,500.00 |
| Pears | $ 6,000.00 | $ 4,000.00 | $ 10,000.00 | 0 | 0 | 0 | 0 | 0 | 0 | $ 10,000.00 |
| Fruits | $ 53,500.00 | $ 17,000.00 | $ 70,500.00 | $ 6,000.00 | 0 | $ 6,000.00 | $ 12,000.00 | 0 | $ 12,000.00 | $ 88,500.00 |
| Carrots | 0 | 0 | 0 | $ 1,000.00 | 0 | $ 1,000.00 | $ 7,000.00 | $ 3,500.00 | $ 10,500.00 | $ 11,500.00 |
| Cucumbers | 0 | 0 | 0 | 0 | $ 5,000.00 | $ 5,000.00 | 0 | 0 | 0 | $ 5,000.00 |
| Lettuce | 0 | 0 | 0 | 0 | 0 | 0 | 0 | $ 23,000.00 | $ 23,000.00 | $ 23,500.00 |
| Vegetables | 0 | 0 | 0 | $ 1,000.00 | $ 5,000.00 | $ 6,000.00 | $ 7,000.00 | $ 26,500.00 | $ 33,500.00 | $ 39,500.00 |
| Total Products | $ 53,500.00 | $17,000.00 | $ 70,500.00 | $ 7,000.00 | $ 5,000.00 | $ 12,000.00 | $ 19,000.00 | $ 26,500.00 | $ 45,500.00 | $ 128,000.00 |
Sum sales by region, product and time
This example uses the SUM function to sum values for each combination of product, region, and time. The page dimensions are line items and time. The Sales line item and FY20 are selected in the context selectors. This means that only sales that took place in 2020 display in this table.
On rows, the Products list displays, and on columns, the Region list displays.
The formula used for the Sales line item is:
Transactions Module.Sales[SUM:Transactions Module.Product, SUM:Transactions Module.Region, SUM: Transactions Module.Sale date]
| London | Birmingham | UK | Paris | Lyon | France | Munich | Berlin | Germany | Total Company | |
| Apples | $ 16,500.00 | 0 | $ 16,500.00 | $ 4,500.00 | 0 | $ 4,500.00 | 0 | 0 | 0 | $ 21,000.00 |
| Peaches | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Bananas | $ 12,000.00 | $ 5,000.00 | $17,000.00 | 0 | 0 | 0 | 0 | 0 | 0 | $ 17,000.00 |
| Pears | $ 6,000.00 | $ 4,000.00 | $ 10,000.00 | 0 | 0 | 0 | 0 | 0 | 0 | $ 10,000.00 |
| Fruits | $ 34,500.00 | $ 9,000.00 | $ 43,500.00 | $ 4,500.00 | 0 | $ 4,500.00 | 0 | 0 | 0 | $ 48,000.00 |
| Carrots | 0 | 0 | 0 | 0 | 0 | 0 | $ 7,000.00 | 0 | $ 7,000.00 | $ 7,000.00 |
| Cucumbers | 0 | 0 | 0 | 0 | $ 2,000.00 | $ 2,000.00 | 0 | 0 | 0 | $ 2,000.00 |
| Lettuce | 0 | 0 | 0 | 0 | 0 | 0 | 0 | $ 11,000.00 | $ 11,000.00 | $ 11,000.00 |
| Vegetables | 0 | 0 | 0 | 0 | $ 2,000.00 | $ 2,000.00 | $ 7,000.00 | $ 11,000.00 | $ 18,000.00 | $ 20,000.00 |
| Total Products | $ 34,500.00 | $9,000.00 | $ 43,500.00 | $ 4,500.00 | $ 2,000.00 | $ 6,500.00 | $ 7,000.00 | $ 11,000.00 | $ 18,000.00 | $ 68,000.00 |
