The SUM aggregation function sums values in a result module based on mapping from a source module.
SUM
For example, you can use the SUM function to sum revenue across several product categories.
值之和[SUM: Mapping, SUM: Mapping 2, etc.]
Arguments
| Argument | Data type | Description |
| 值之和 | Number | The values to sum based on theMappingargument. |
| 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值之和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 theMappingargument.
Syntax example
'Employee Expenses'.Salary[SUM:'Employee Details'.Region, SUM: 'Employee Details'.Role]
In this example, the values from theSalaryline item are summed for each employee role and region.
Additional information
Summary method
Sumcan also be used as asummary 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 differentaggregation function.
To determine whether to use SUM orLOOKUP, seeFormula 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 isproduction data, which can change and make your formula invalid.
Constraints
The dimensions of theMapping参数μst also be dimensions of the值之和argument.
Excel等效
Examples
In these examples, there are several modules.
The first is aTransactions Module. In this module, theTransactionslist displays on rows. Several line items that contain information about each transaction display on columns:
- Productline item, which has the list data type with theProductslist. Displays the product that each transaction relates to.
- Region, which has the list data type with theOrganizationlist. 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 | 6000美元 | 4/1/2020 |
| Transaction 04 | Pears | Birmingham | 4000美元 | 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 | 柏林 | $ 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 | 柏林 | $ 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 | 柏林 | $ 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 |
TheTransactions Moduleis used as the source for each of the examples below.
Sum sales by region
This example uses the SUM function to sum values from theTransactions Modulefor each region.
The formula used for theTotal Salesline item is:
Transactions Module.Sale value[SUM:Transactions Module.Region]
| London | Birmingham | UK | Paris | Lyon | France | Munich | 柏林 | 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 theSalesline item. On rows, theProductslist displays, and on columns, theRegionlist displays.
The formula used for theSalesline item is:
Transactions Module.Sale value[SUM:Transactions Module.Product, SUM:Transactions Module.Region]
| London | Birmingham | UK | Paris | Lyon | France | Munich | 柏林 | 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 | 6000美元 | 4000美元 | $ 10,000.00 | 0 | 0 | 0 | 0 | 0 | 0 | $ 10,000.00 |
| Fruits | $ 53,500.00 | $ 17,000.00 | $ 70,500.00 | 6000美元 | 0 | 6000美元 | $ 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 | 6000美元 | $ 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. TheSalesline item andFY20are selected in the context selectors. This means that only sales that took place in 2020 display in this table.
On rows, theProductslist displays, and on columns, theRegionlist displays.
The formula used for theSalesline 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 | 柏林 | 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 | 6000美元 | 4000美元 | $ 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 |
Disclaimer
We update Anapedia content regularly to provide the most up-to-date instructions.