List.Sum Function in Power Query M (5 Easy Examples)

List.Sum adds up the numbers in a list and returns the total. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to total a column of values or add up a set of numbers inside a query, this is the function you reach for.

Table of Contents

Syntax of List.Sum Function

List.Sum(list as list, optional precision as nullable number) as any

where

list (required, list). The list of numbers you want to add up.
precision (optional, nullable number). Controls the arithmetic precision. Pass Precision.Double for standard floating-point math (the default when omitted) or Precision.Decimal for exact decimal math.

Returns: the sum of the non-null numbers in the list. If the list has no non-null values, it returns null.

In plain terms, you hand it a list of numbers and it gives you the total, quietly skipping any null values along the way.

Example 1: Sum a list of numbers

Add up a plain list of five numbers.

List.Sum({12,8,5,20,15})

Result: 60

It adds every value in the list and hands back the total.

Example 2: Sum a table column

The most common use is totaling one column of a table.

Say you have an Expenses query with a Category and an Amount column.

Here is the starting data:

Category	Amount
Rent	1200
Utilities	180
Groceries	340
Transport	95
Internet	60

Pass the column to List.Sum using the Source[Column] syntax:

let
    Source = Excel.CurrentWorkbook(){[Name="Expenses"]}[Content],
    Result = List.Sum(Source[Amount])
in
    Result

Source[Amount] pulls the whole Amount column out as a list, which List.Sum then totals.

Result: 1875

Example 3: Add a per-row total column

You can also sum across columns within each row by wrapping List.Sum in Table.AddColumn.

Say you have a Quarters query with sales for each region across four quarters.

Here is the starting data:

Region	Q1	Q2	Q3	Q4
North	500	620	580	700
South	410	390	460	520
East	300	350	280	410

Build a list of the four quarter values for each row and total it:

let
    Source = Excel.CurrentWorkbook(){[Name="Quarters"]}[Content],
    #"Added YearTotal" = Table.AddColumn(Source, "YearTotal", each List.Sum({[Q1],[Q2],[Q3],[Q4]}), type number)
in
    #"Added YearTotal"

For each row, {[Q1],[Q2],[Q3],[Q4]} builds a small list from that row’s values, and List.Sum totals it.

The result adds a YearTotal column:

Region	Q1	Q2	Q3	Q4	YearTotal
North	500	620	580	700	2400
South	410	390	460	520	1780
East	300	350	280	410	1340

Example 4: Nulls are ignored, not propagated

A null in the list does not break the total. List.Sum simply skips it.

List.Sum({10,null,5,null,20})

Result: 35

The two null values are skipped, so you get 10 + 5 + 20. This trips people up. They expect a single null to make the whole result null, and it does not.

Example 5: An all-null list returns null

There is one case where you do get null: when the list has no non-null numbers at all.

List.Sum({null,null,null})

Result: null

With nothing to add, the function returns null rather than 0. An empty list {} returns null for the same reason.

Things to keep in mind with List.Sum

An all-null or empty list returns null. If you need 0 instead, wrap the call: List.Sum(myList) ?? 0. To clear nulls from the data first, you can remove null values in Power Query.
Non-number values throw. A list with text in it fails with Expression.Error: We cannot convert the value "abc" to type Number. Clean or convert the values first.
Use Precision.Decimal for money. Default floating-point math can introduce tiny rounding errors on currency. Pass Precision.Decimal as the second argument when exact decimal sums matter.

Common questions about List.Sum

How do I sum a column with a condition, like only one category?

Filter the table first with Table.SelectRows, then sum: List.Sum(Table.SelectRows(Source, each [Category]="Rent")[Amount]). For a grouped total per category, Table.Group with List.Sum as the aggregate is the cleaner option.

How is this different from finding the maximum in a column?

List.Sum totals every value, while finding the maximum value in a column returns just the single largest one. Both work on a column passed as a list.

What is the difference between List.Sum and List.Average?

List.Sum returns the total of the values; List.Average returns their mean. Both ignore null values the same way.

List of All Power Query Functions

Related Power Query Functions / Articles: