PowerApps Sum function (Get sum of a column PowerApps)

In this PowerApps Tutorial, We will discuss what is PowerApps Sum function, What are Sum function syntaxes in Powerapps, and how to get sum of a column in PowerApps.

Also, By taking some simple scenarios, We will cover these below topics that are related to the PowerApps Sum function as:

PowerApps Sum function

• Sum function in PowerApps, is a type of function that helps to calculate the sum of its arguments.
• For example, suppose I will supply the values for the PowerApps Sum function as Sum(10, 20, 30) then the output returns as 60.
• Below represents the PowerApps Sum function syntax as:

Syntax-1:

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

Where,

NumericalFormula(s) = You need to specify some numeric values that you wants to perform the sum operation.

Syntax-2:

Sum( Table, NumericalFormula )

Where,

1. Table = Specify the Data source that you want to operate on. The Data source may be a SharePoint Online List, Excel spreadsheet, etc.
2. NumericalFormula = Here, you can specify the column of the table (Data source) that you want to perform with the sum operation.

Example:

• I have a SharePoint Data source named Products. This list has a Quantity column (Number Data type) and Price column (Number Data type) as like the below screenshot:

• Here what I want to do is, I want to multiply both the column values (Quantity and Price) and display the total sum values in a PowerApps Label input control.
• For that, Insert a Label control and apply this below formula on its Text property as:

Text = Sum(Products, Quantity*Price)

Where,

1. Products = SharePoint Online list
2. Quantity and Price = Number column from the SharePoint list

Once you will apply the formula, the total sum value will display in the Label control as like the above screenshot.

Some important Points to remember while using PowerApps Sum function:

• Whenever you are using the Average, Max, Min, and Sum function in PowerApps, it can be delegated when used with a data source that supports delegation for these functions.
• Whenever you are trying to use these types of functions in Powerapps, a delegation warning will display that will remind you of the limitation and to suggest switching to delegable alternatives where possible.
• To overcome the Powerapps Delegation issue, you can extend the Data row limit. Go to File tab -> Settings -> Advanced settings -> Make the Value as 2000 under the Data row limit for non-delegable queries. For more further queries about the Delegation, you can refer to this link: PowerApps Delegation

PowerApps sum datatable column

Here in this example, we will see how to work with the PowerApps sum datatable column.

• On the PowerApps screen, I have a Powerapps Data table that contains some records. That data table records are retrieving from the SharePoint list named Bank Depositor Info.
• This Data table has these many below columns:

1. Title (Depositor Name)
2. Transaction Type
3. Date
4. Amount

Below screenshot represents the PowerApps Data table:

NOTE:

In case you want to format the Amount number as Currency in the Powerapps Data table, then you can follow this link: How to format number as currency in PowerApps

• Next, I want to sum the data table column Amount based on deposit or withdrawal by Date to get the total amount of money deposited or withdrawn. That means, whenever I will choose any particular date, then it will display the total amount of details of that specified date.
• To do this, I will explain to you two different ways that you can follow as:

If you want to provide the date as manually like “2020-10-01“, then follow this below things:

• Insert a Label control and apply this below formula on its Text property as:

Text = Sum(Filter('Bank Depositor Info', Date=Date(2020,10,11)), Amount)

Where,

1. ‘Bank Depositor Info’ = SharePoint list Data source
2. Date = This is the Date picker column that I have created in the SharePoint list
3. Date(2020,10,11) = It is the function that we need to specify a manual date
4. Amount = This is the column that we want to calculate the total value

• Once you will apply the above formula, then you can see the total amount (of that specified manual date) will display in the Label control as shown below.

• Suppose you have a PowerApps Date Picker control and you want to select a particular date from that date picker. When you will select a particular date, then the total amount will display in a Label control (of that selected date).
• For this, Select the Label control and apply this below formula on its Text property as:

Text = Sum(Filter('Bank Depositor Info', Date=DatePicker1.SelectedDate), Amount)

Where,

DatePicker1 = Date picker control name

• Save and Preview (F5) the app. Select any date from the date picker control, then you can see the total amount value (of that selected date) in the Label control as shown in the below screenshot.

PowerApps sum column values

In this below scenario we will learn how to sum a column values using Powerapps.

• I have a PowerApps Data table that filters a SharePoint list using the Powerapps Combobox control. That means, when I will choose a name from the Combobox control, it filters and displays the details in the Data table as shown below.
• Also, I want a Label control that will display the total number of Amount that presents in the Data table. As I filtered three records, So there are three individual amounts displays in the data table i.e. “5000”, “7000” and “3000”.
• So what formula would I use in a label control, so the total amount will display as “15000”.

• To work with this scenario, you can apply this below formula on Label’s Text property as:

Text = Sum(
    Filter(
        'Bank Depositor Info',
        Title in ComboBox1.SelectedItems
    ),
    Amount
)

Where,

1. ‘Bank Depositor Info’ = Data source name (SharePoint list name)
2. Title = This is the Sharepoint column that I used to filter reference in the Combobox control.
3. ComboBox1 = Combobox control name
4. Amount = This is the SharePoint column that I want to calculate the total value

You can refer to the below screenshot.

• Now Save and Preview the app. Select any title or name from the Combobox control.
• Once you will select the name, then the specific user details will add on the PowerApps table. Then you can see all the user’s total amount will appear in the Label control as shown below.

PowerApps sum column in collection

This below scenario describes how a user can work with the Powerapps sum column in Powerapps collection. Here, We will see two different types of scenarios as:

1. PowerApps Sum of a Collection
2. PowerApps Sum of a Collection with conditions

1. PowerApps Sum of a Collection:

• I have a PowerApps Collections named priceCollection. This collection is having all the records from a SharePoint list named Products.
• The Powerapps collection will create when a user will click on the button (PowerApps Sum Column in Collection) as shown below.

• As in this below screenshot, you can see the PowerApps collections (priceCollection). Here, I want to sum all of the numbers that are present under the Price column.

• To work with this scenario, I have taken a Label control and applied this below formula on its Text property as:

Text = Sum(priceCollection, Price)

Where,

1. priceCollection = Powerapps Collection name
2. Price = This is the column that you want to sum all of the numbers from the Powerapps collection itself

• When you will Save and preview the app, you can see the total price (from the Collection) will display in the Label control.

2. PowerApps Sum of a Collection with conditions:

• So in the previous example, we saw the PowerApps Sum of a Collection without any conditionality. Here in this scenario, We will learn how to work with the sum of a Powerapps collection with conditions.
• The condition is, How to SUM a column of a Collection depending on other values within the Powerapps Collection.
• The below screenshot represents a PowerApps collection named amountCollection. All the records are coming from a Sharepoint list.
• This Powerapps Collection has a column named Transaction Type that is having two different types of transactions as “Deposit” and “Withdrawal“.
• Here, I would like to be able to SUM all the Amounts of Deposit and Withdrawal separately. That means the total amount for Deposit should be 23000 and 10000 for Withdrawal.

• To find out the solution, you can use a Dropdown control and a filter should be used within the sum function.
• Insert a Dropdown control and provide all the Transaction type values on its Items property as like the below screenshot.

• Add a Label control (that will help you to display the total amount from the collection) and set its Text property as:

Text = Sum(
    Filter(
        amountCollection,
        TransactionType = ddTransactionType.Selected.Value
    ),
    Amount
)

Where,

1. amountCollection = Powerapps Collection name
2. ddTransactionType = Dropdown control name
3. Amount = This is the column that you want to calculate the total value from the Powerapps collection (depending upon the Transaction type)

• Save and Preview the app. When you will select Deposit from the Transaction Type dropdown control, the total amount (of Deposit only) will appear in the Label control.
• Similarly, When you will select Withdrawal from the Transaction Type dropdown control, the total amount (of Withdrawal only) will appear in the Label control as shown below.

PowerApps sum gallery

Now we will learn how we can calculate sum of the value in a PowerApps Gallery Control.

• I have a PowerApps collection (priceCollection) that is having all the records from a SharePoint list data source. Refer to the below screenshot.
• First of all, I want to multiply the Price and Quantity column from the collection and store the value in another column named ExtendedPrice. Also, I want to display all the customer details and the Extended price in a gallery control.
• After multiplying all the value from the Collection, I want to calculate the grand total price of all customers, and the total price will display in a Label control. Follow the below steps that we need to do.

• At first, Insert a Gallery control and apply this formula on its Items property as:

Items = AddColumns(
    priceCollection,
    "ExtendedPrice",
    Quantity * Price
)

Where,

1. AddColumns = Powerapps AddColumns is a function that helps to add a column to a table.
2. priceCollection = Powerapps Collection name
3. “ExtendedPrice” = This is the new column that I have added where it will store the multiply value (of quantity and price column)
4. Quantity, Price = These are the columns (from Powerapps Collections) that I want to multiply

• Next, Take a Gallery control and set its Layout to “Title, subtitle, and body“.
• Select a Label control from the Gallery control and set its Text property as:

Text = ThisItem.ExtendedPrice

• Once you will save and preview the app, you can see all each multiplied value will display in the Gallery control as shown below.

• Next, suppose you want to display the grand total of extended price outside of the Gallery. Then, in that case, Insert a Label control and apply this below formula on its Text property as:

Text = Sum(Gallery3.AllItems, ExtendedPrice)

Where,

1. Gallery3 = Powerapps Gallery control name

• When you will preview the app, you can see the grand total value will appear in the Label control.

PowerApps sum SharePoint column

In this below example, We will learn how to work with PowerApps Sum function using a SharePoint list column.

• I have a SharePoint Online list named Bank Depositor Info. This list has these many below columns and records. Among all the records, I want to calculate the total amount of a Depositor Name whose name is Preeti. Simply, We can say it will only sum of Preeti’s total amount.

• Also, on the Powerapps screen, I have a Data table that is having all the records from the SharePoint list data source (“Bank Depositor Info”).
• Another Label control is there that will help to display the total amount of Preeti.

• Select the Label control and apply this below formula on its Text property as:

Text = Sum(Filter('Bank Depositor Info', Title = "Preeti"), Amount)

Where,

1. ‘Bank Depositor Info’ = SharePoint list name
2. Title = This is the SharePoint list column (Depositor name) that helps to filter the name as Preeti
3. Amount = This is the column that I want to calculate the total value

• When you will preview the app, then you can see the total amount of preeti in the Label control as like above screenshot.

PowerApps find max value in column

PowerApps Max function helps to find the maximum value. It can find the maximum of all values from any data sources like SharePoint list, Excel spreadsheet, etc.

PowerApps Max value Syntax

Below represents the Powerapps Max value syntaxes:

Syntax-1:

Max( NumericalFormula1, [ NumericalFormula2, ... ] )

NumericalFormula(s) = Specify some numeric values that you wants to perform the Max operation.

Syntax-2:

Max( Table, NumericalFormula )

1. Table = Specify the Data source that you want to operate on. The Data source may be a SharePoint Online List, Excel spreadsheet, etc.
2. NumericalFormula = You can specify the column of the table (Data source) that you want to perform with the Max operation.

Example:

• This below is a SharePoint list (Products). It has a column named Delivery Charges (with Number Datatype).
• Here, I want to find the maximum value or maximum delivery charges using the PowerApps Max function. As 25 is the highest maximum value, So I want to display this value in a Powerapps Label control.

• You can see there is a Label control in the Powerapps screen that will help you to display the highest delivery charges value.

• Select the Label control and apply this below formula on its Text property as:

Text = Max(Products, 'Delivery Charges')

Where,

• Products = SharePoint List name
• ‘Delivery Charges’ = SharePoint column name where I wanted to find out the highest or maximum value

• Also, you can apply this formula on Label’s Text property as:

Text = First(Sort(Products,'Delivery Charges',Descending)).'Delivery Charges'

• Once you will Save and preview the app, you can see the highest or maximum Delivery charges value in the Label control.

PowerApps sumif

Below is an example that how you can work with the PowerApps Sumif function.

• There is a Data table on the PowerApps screen that is having the records from an Excel spreadsheet data source (Worker Info). You can see there are some columns like Member, Number of Days, Minutes, and Organization.
• In this scenario, I want to calculate the total sum of minutes of individual members. That means, When I will select the member name as Preeti, then the total sum of minutes (of Preeti only) will display in a Label control.

• I have a Powerapps Combobox control that helps to display the member names from the Excel sheet. As the Excel sheet is having a repeated name and though we do not need to display the repeated name in Combobox, So I provided the distinct value to the Combobox control as:

Items = Distinct(WorkerInfo.Member,Member)

• Just make disable or toggle off to the Allow multiple selections option from the Combobox Properties pane (select Combobox control -> Properties -> Allow multiple selections). If you will disable it, then the Combobox will allow only a single value or member.
• Once you will apply this above formula, then you can see the distinct member in the Combobox control (by clicking on the dropdown).

• Next, as I wanted to calculate the total minutes of individual members and display it in a Label control, apply this below formula on Label’s Text property as:

Text = Sum(
    Filter(
        WorkerInfo,
        Member = ComboBox2.Selected.Result
    ),
    Minutes
)

Where,

1. WorkerInfo = Excel spreadsheet name
2. Member = This is the column that is having all the member names (present in the Excel sheet)
3. ComboBox2 = Combobox control name
4. Minutes = This is the column that I want to calculate the sum value (present in the Excel sheet)

• Save and Preview the app. Select a Member from the Combo box control. When you will select, then the total minute value (of that selected member) will display in the Label control as shown below.

Also, you may like these below PowerApps Tutorials:

• PowerApps upload file to SharePoint document library
• PowerApps Get Current User (ID, EMail, Department, Location, Photo, etc)
• PowerApps send email on submit
• PowerApps Email Attachment Control – How to Use
• PowerApps Microphone Control – How to use
• How to Create Login Screen in PowerApps
• PowerApps Popup message Box with Examples
• Get users from SharePoint Group in PowerApps
• PowerApps: Submit data to two SharePoint Lists
• PowerApps Patch Function with examples
• PowerApps set field value based on another field

In this PowerApps Tutorial, We discussed what is PowerApps Sum function and what are the Sum function syntaxes in Powerapps.

Also, we learned with these below topics as: