1. Calculation functions
  2. All Functions
  3. Aggregation Functions
  4. SUM

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

ArgumentData typeDescription
Values to sumNumberThe values to sum based on the Mapping argument.
MappingList, 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

SUMIF

Related Anaplan functions

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.

ProductRegionSale valueSale date
Transaction 01ApplesLondon$ 10,000.001/23/2020
Transaction 02BananasBirmingham$ 5,000.002/15/2020
Transaction 03PearsLondon$ 6,000.004/1/2020
Transaction 04PearsBirmingham$ 4,000.005/15/2020
Transaction 05ApplesParis$ 4,500.005/19/2020
Transaction 06CarrotsMunich$ 7,000.005/20/2020
Transaction 07LettuceBerlin$ 11,000.005/26/2020
Transaction 08BananasLondon$ 12,000.005/28/2020
Transaction 09ApplesLondon$ 6,500.005/29/2020
Transaction 10CucumbersLyon$ 2,000.005/30/2020
Transaction 11CarrotsParis$ 1,000.003/4/2021
Transaction 12CarrotsBerlin$ 3,500.003/20/2021
Transaction 13BananasBirmingham$ 8,000.004/1/2021
Transaction 14ApplesLondon$ 9,500.006/15/2021
Transaction 15PeachesMunich$ 5,000.006/28/2021
Transaction 16PearsMunich$ 7,000.007/7/2021
Transaction 17LettuceBerlin8/11/20218/11/2021
Transaction 18CucumbersLyon9/22/20219/22/2021
Transaction 19BananasParis9/30/20219/30/2021
Transaction 20ApplesLondon10/1/202110/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 Sales line item is:

Transactions Module.Sale value[SUM:Transactions Module.Organization]


LondonBirminghamUKParisLyonFranceMunichBerlinGermanyTotal 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.Sales[SUM:Transactions Module.Product, SUM:Transactions Module.Region]


LondonBirminghamUKParisLyonFranceMunichBerlinGermanyTotal Company
Apples$ 35,000.000$ 35,500.00$ 4,500.000$ 4,500.00000$ 40,000.00
Peaches000000$ 12,000.0000$ 12,000.00
Bananas$ 12,000.00$ 13,000.00$25,000.00$ 1,500.000$ 1,500.00000$ 26,500.00
Pears$ 6,000.00$ 4,000.00$ 10,000.00000000$ 10,000.00
Fruits$ 53,500.00$ 17,000.00$ 70,500.00$ 6,000.000$ 6,000.00$ 12,000.000$ 12,000.00$ 88,500.00
Carrots000$ 1,000.000$ 1,000.00$ 7,000.00$ 3,500.00$ 10,500.00$ 11,500.00
Cucumbers0000$ 5,000.00$ 5,000.00000$ 5,000.00
Lettuce0000000$ 23,000.00$ 23,000.00$ 23,500.00
Vegetables000$ 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]


LondonBirminghamUKParisLyonFranceMunichBerlinGermanyTotal Company
Apples$ 16,500.000$ 16,500.00$ 4,500.000$ 4,500.00000$ 21,000.00
Peaches0000000000
Bananas$ 12,000.00$ 5,000.00$17,000.00000000$ 17,000.00
Pears$ 6,000.00$ 4,000.00$ 10,000.00000000$ 10,000.00
Fruits$ 34,500.00$ 9,000.00$ 43,500.00$ 4,500.000$ 4,500.00000$ 48,000.00
Carrots000000$ 7,000.000$ 7,000.00$ 7,000.00
Cucumbers0000$ 2,000.00$ 2,000.00000$ 2,000.00
Lettuce0000000$ 11,000.00$ 11,000.00$ 11,000.00
Vegetables0000$ 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