Live training: next batches coming soon. Join the waitlist:Waitlists open:Developer Training →Developer →Copilot Studio Training →Copilot Studio →

Power Apps Sum Function: Total a Collection, Gallery or SharePoint Column (Build Along)

Our help desk lead wanted a running total under the equipment orders screen, so nobody had to add up line items by hand. She also wanted the total for one region at a time, shown as dollars.

In this tutorial, we’ll build that screen with the Power Apps Sum function. You’ll total a collection column, add a SUMIF-style condition, sum what a gallery shows, format the result as currency, and see what really happens when Sum runs against a SharePoint list with 623 items.

Here’s what we’re building. Every number on it comes from a Sum formula:

Finished Power Apps SumScreen in Preview with order totals, a West region sum and a gallery total of $2,105.96

Before You Start

You need a Power Apps license that includes SharePoint (most Microsoft 365 business plans do) and edit rights on a SharePoint site. If canvas apps are new to you, our Power Apps tutorials hub has the basics.

My Service Requests list has no number or currency column; its only numeric field is ID. So the order data lives in a collection, which is a table the app keeps in memory. Our guide to Power Apps collections explains them. Here are the columns:

NameTypeNotes
ItemTextLaptop stand, USB-C dock, Webcam
QtyNumber4, 2, 10 (one row is blank on purpose)
UnitPriceNumber (USD)49.99, 129.50, 74.25
RegionTextWest, East or Central

And these are the seven sample orders we’ll load:

ItemQtyUnitPriceRegion
Laptop stand4$49.99West
USB-C dock2$129.50East
Wireless mouse10$24.95West
27-inch monitor3$219.00Central
Headset5$59.00East
Webcam6$74.25West
Keyboard(blank)$45.00Central

Power Apps Sum function syntax

Sum has two forms. The first adds separate values, and the second runs a formula for every record in a table and adds the results:

Sum( NumericalFormula1, [ NumericalFormula2, ... ] )
Sum( Table, NumericalFormula )

In the table form, you name columns directly, such as Qty or Qty * UnitPrice. The table can be a collection, a filtered table, a gallery’s AllItems or a data source.

Step 1: Create the App and Connect the List

New to canvas apps? Start here. I reuse the same Service Desk Demo app from the Power Apps Choices function walkthrough. A data source is the place the app reads its data from.

1. Open the Service Requests list on your SharePoint site and check its columns:

SharePoint Service Requests list with Status and Priority columns used as the Sum demo data source

2. Go to make.powerapps.com and find Start with design on the Home page:

Power Apps maker portal Home page with the Start with design option for a new canvas app

3. Select Create in the left menu, then Create from blank:

Power Apps Create page with Create from blank selected to start the Sum demo app

4. In Start with a blank canvas, pick Tablet. Power Apps Studio opens:

Start with a blank canvas dialog with the Tablet layout chosen for the Sum function app

5. Select Add data, search for SharePoint and pick the connector:

Add data pane in Power Apps Studio searching for the SharePoint connector

6. Paste your site URL under Connect to a SharePoint site and select Connect:

Connect to a SharePoint site panel with the SPGuides site URL entered

7. Check Service Requests under Choose a list and select Connect:

Choose a list panel with the Service Requests list checked before Connect

8. Add a blank screen with New screen > Blank and rename it SumScreen. The Insert menu has the Text label and Button controls we need:

Power Apps Insert menu with Text label and Button controls highlighted

Two parts of Studio come up in every step. The formula bar (marked fx) sits above the canvas, and the property dropdown on its left picks the property you’re editing, such as Text or OnSelect. Press Ctrl+S to save often.

Checkpoint: Service Requests shows in the Data pane, and SumScreen is selected in the Tree view.

Step 2: Load the Sample Orders Into a Collection

A button fills the collection, so you can reload the data any time you test.

  1. Insert a Button, rename it btnLoadOrders and set its Text to “Load sample orders”.
  2. Pick OnSelect in the property dropdown and paste the formula below.
  3. Add a label with the Text "colOrders rows = " & CountRows(colOrders) so you can see the load worked.
ClearCollect(colOrders,
    {Item: "Laptop stand", Qty: 4, UnitPrice: 49.99, Region: "West"},
    {Item: "USB-C dock", Qty: 2, UnitPrice: 129.50, Region: "East"},
    {Item: "Wireless mouse", Qty: 10, UnitPrice: 24.95, Region: "West"},
    {Item: "27-inch monitor", Qty: 3, UnitPrice: 219.00, Region: "Central"},
    {Item: "Headset", Qty: 5, UnitPrice: 59.00, Region: "East"},
    {Item: "Webcam", Qty: 6, UnitPrice: 74.25, Region: "West"},
    {Item: "Keyboard", Qty: Blank(), UnitPrice: 45.00, Region: "Central"}
)

With real data, you’d create a collection from a SharePoint list instead. Here’s the button selected with OnSelect in the property dropdown:

Load sample orders button selected with the ClearCollect colOrders formula in its OnSelect property

Select Preview (the play button at the top right) or press F5, then select the button. Press Esc to return to the editor.

Checkpoint: the label reads colOrders rows = 7.

Step 3: Sum a Collection Column

Now the main formula. Insert a Text label, keep Text in the property dropdown, and type:

Sum(colOrders, Qty * UnitPrice)

Sum works out Qty * UnitPrice for each order and adds the seven results. To total one column, pass just its name, so Sum(colOrders, Qty) returns 30. The blank Keyboard quantity is skipped.

Label selected with Sum(colOrders, Qty * UnitPrice) in the formula bar showing 2105.96

Want the line total as its own column in a table? The Power Apps AddColumns function adds it, and you can still sum that column afterwards.

Checkpoint: the label shows 2105.96, and the Qty label shows 30.

Step 4: Add a Condition With Sum(Filter()) (SUMIF)

Power Apps has no SUMIF function. You filter the table first and then sum it. Add another label with this Text:

Sum(Filter(colOrders, Region = "West"), Qty * UnitPrice)

Watch the parentheses. The column or formula goes in as the second argument of Sum, so Sum(Filter(...)).Qty isn’t valid syntax. Separate several conditions with commas inside Filter, and they all must be true.

Sum Filter formula for the West region selected in Power Apps Studio with the result 894.96

The same pattern works with a drop-down instead of a fixed “West”. See how to apply multiple filters on a Power Apps gallery for more conditions.

Checkpoint: the label shows 894.96, which is Laptop stand $199.96 + Wireless mouse $249.50 + Webcam $445.50.

Step 5: Format the Total as Currency

A raw 2105.96 looks unfinished. Wrap Sum in the Text function with a number format:

Text(Sum(colOrders, Qty * UnitPrice), "$#,##0.00")

The format adds the dollar sign, a thousands separator and two decimals. Our Power Apps currency format guide covers more formats.

Text and Sum formula with the $#,##0.00 format turning the order total into $2,105.96

Checkpoint: the label reads $2,105.96.

Step 6: Sum the Items in a Gallery

Totals often need to follow what the user sees. A gallery’s AllItems property holds the rows it currently shows, so summing it respects any filter.

  1. Insert a Drop down named ddRegion with Items ["All", "West", "East", "Central"].
  2. Insert a Vertical gallery named galOrders and set its Items to the first formula below.
  3. Add a label under the gallery with the second formula as its Text.
If(ddRegion.Selected.Value = "All", colOrders,
    Filter(colOrders, Region = ddRegion.Selected.Value))
"Gallery total: " & Text(Sum(galOrders.AllItems, Qty * UnitPrice), "$#,##0.00")
Gallery total label selected with Sum of galOrders.AllItems in the formula bar showing $2,105.96

Now preview the app and pick West in the drop-down. The gallery drops to three orders, and the total follows:

Power Apps gallery filtered to the West region with a gallery total of $894.96 for three rows

Using a data table instead of a gallery? See how to sum a column in a Power Apps data table.

Checkpoint: All shows $2,105.96, and West shows $894.96.

Step 7: Sum Plain Numbers, Text Inputs and Blanks

The first Sum form adds separate values. I added one label per test, and these are the real results from the app:

FormulaResult
Sum(1, 2, 3)6
Sum([10, 20, 30], Value)60 (a single-column table)
Sum(1, Blank(), 3)4 (blank is skipped)
Sum(1, "2", 3)6 (numeric text was converted)
Sum(1, "abc", 3)Error: The value ‘abc’ cannot be converted to a number.
Max(colOrders, UnitPrice)219

For text inputs, convert first with the Power Apps Value function. With 4 and 6 typed in, this returns 10. An empty box counts as blank, so the sum dropped to 6:

Sum(Value(txtQtyA.Text), Value(txtQtyB.Text))

Typing abc into a box raised the same conversion error banner. Max, Min and Average take the same two forms as Sum, which is why Max returned the highest unit price, $219.00.

Checkpoint: Sum(1, 2, 3) shows 6 and the text-input label shows 10.

Step 8: Sum a SharePoint Column and Check Delegation

Delegation means Power Apps sends the work to the data source instead of downloading rows. SharePoint can’t run Sum for the app, as our Power Apps delegation guide explains. To see it, I summed the list’s ID column:

Sum('Service Requests', ID)

Studio underlines the formula with a yellow delegation warning. Hover it to read the message:

Delegation warning on Sum('Service Requests', ID): the Sum operation is not supported by this connector

The label showed 125250. That’s 1 + 2 + … + 500, because only the first 500 rows came down. The list really has 623 items, and CountRows showed 500 too.

Next, I raised the limit in Settings > General > Data row limit to 2000 and refreshed the data source. Our Power Apps data row limit post explains the setting:

Power Apps Settings General page with the Data row limit set to 2000

Now the same formula returned 194376 and CountRows returned 623, both correct. The warning stays, because the app still downloads the rows and sums them locally:

Sum of the Service Requests ID column showing 194376 and CountRows 623 after raising the data row limit

I set the limit back to 500 afterwards. Loading the list into a collection doesn’t fix this, because ClearCollect is capped by the same limit.

Checkpoint: at limit 500 you see 125250 with a yellow warning; at 2000 you see 194376.

Test the App

Select Preview, select Load sample orders, then change the Region drop-down. In the editor you can also hold Alt and select a button. These are the results my app showed:

FormulaResult
Sum(colOrders, Qty * UnitPrice)2105.96
Sum(colOrders, Qty)30
Sum(Filter(colOrders, Region = "West"), Qty * UnitPrice)894.96
Text(Sum(...), "$#,##0.00")$2,105.96
Gallery total, West selected$894.96 (3 rows)
Sum('Service Requests', ID), limit 500125250 (wrong, warning)

Save with Ctrl+S when you’re happy. Preview is enough for testing; you don’t need to publish first.

Make It Production-Ready

  • Keep money math in the collection or the gallery, not in a SharePoint list over 500 rows, unless every row arrives.
  • Store prices in Number or Currency columns. Text prices force Value() everywhere and break on bad input.
  • Wrap user input in IfError, for example IfError(Sum(Value(txtQtyA.Text), 0), 0), so one typo doesn’t show an error banner.
  • Format only for display. Keep the raw Sum in variables or Patch calls, and apply Text() in the label.

Troubleshooting

  • The total is too low on a big list. Sum isn’t delegated to SharePoint, so only the first 500 (or 2,000) rows are added.
  • “The value ‘abc’ cannot be converted to a number.” A text value reached Sum. Clean the input or use Value() inside IfError.
  • The gallery total ignores the filter. You summed the source table. Sum galOrders.AllItems instead.
  • Your count looks capped at 500. The Power Apps CountRows function has the same delegation limit on SharePoint.

Where to Take It Next

If you’re building more totals screens, these posts are a good next read:

Frequently Asked Questions

How do I sum a column in a Power Apps collection?

Use the table form: Sum(colOrders, Qty). For a calculated total per row, pass a formula, such as Sum(colOrders, Qty * UnitPrice).

Is there a SUMIF function in Power Apps?

No. Filter the table first and sum the result: Sum(Filter(colOrders, Region = “West”), Qty * UnitPrice). In our test, that returned 894.96.

How do I sum a gallery column in Power Apps?

Sum the gallery’s AllItems property, for example Sum(galOrders.AllItems, Qty * UnitPrice). It follows whatever filter the gallery’s Items formula applies.

Is the Sum function delegable with SharePoint?

No. Studio shows a delegation warning saying the Sum operation isn’t supported by this connector, and only rows up to the data row limit are added.

Can Sum add text values?

Sum works on numbers. Numeric text such as “2” was converted in our test, but “abc” raised an error, so use Value(). Microsoft documents the syntax in Average, Max, Min, Sum functions.

4 thoughts on “Power Apps Sum Function: Total a Collection, Gallery or SharePoint Column (Build Along)”

Leave a Comment