How to Sum an Array in Power Automate

Power Automate is a versatile tool that streamlines repetitive tasks allowing users to save time with automation.

However, Power Automate lacks functionality for some simple tasks. One such missing feature is the ability to sum an array in Power Automate.

This article dives into providing an understanding of how to sum an array, utilizing variables and loops to achieve a simple sum.

Key Takeaways

• Power Automate lacks a function or action to sum an array.
• Variables and an Apply to each loop action can be used to iterate through and sum each item in an array.

Overview of How to Sum an Array

In Power Automate, you can sum an array of numbers by using the Initialize variable and Apply to each actions.

The process requires creating variables to hold the total sum and iterating through the array using the Apply to each loop action. Each loop adds the current array item to the total variable, eventually yielding the complete sum. Additionally,

Here is an overview of the required elements in your flow in order to get the sum from an array.

1. You will need an array of numerical values. This could be from an external source such as an Excel table or a SharePoint list. In this example, the array is `[1,2,3,4,5]` from a Compose action and can be referenced with the expression `outputs('Array')`.
2. You will need an Initialize variable action set as either an Integer or Float type. This will be used to hold the total sum of the array as the flow loops through the array.
3. You will need a second Initialize variable action to temporarily store the total in each iteration of the loop.
4. You will need an Apply to each loop action to loop through each value of the array.
5. The Select an output from previous step field for the Apply to each action should contain the array to loop through.
6. You need a Set variable action inside the loop to temporarily store the previous total plus the current item of the array.
7. You need a second Set variable action inside the loop to store the value from the Set variable action in step 6. This is then used as the previous total in the next iteration of the loop.
8. You can then reference the variable in step 7 outside the loop and this will contain the array total.

Initialize Variables for Summing an Array

First, create a new flow and add two Initialize variable actions.

1. Add an Initialize variable action from the Variable connector.
2. Set the Name to something such as ArrayTotal.
3. Set the Type to either Float or Integer. This will depend on the type of data in your array, if your array will only ever contain integer values you can set this to Integer. Otherwise, you should choose Float to accommodate decimal values.
4. Set the starting Value of the variable to `0`.
5. Add a second Initialize variable action with a name such as TempArrayTotal using similar steps 1 to 4.

These will be used inside the loop to calculate the sum of your array.

📝 Note: Two variables are required so you can reference the previous total from the loop while also storing the new total within the current iteration of the loop.

Apply to Each Action to Loop Through the Array

Now you will need to create a loop action to loop through your array and add the current item to the previous total.

• Add the dynamic content reference the array to your Apply to each action.

In this example, the Apply to each action will iterate through each element of the array from the Compose action. But this could be any array of values such as from an Excel or SharePoint list data source.

Set Variables Inside the Apply to Each Loop

Now you can add each item in the array within the loop.

1. Add a Set variable action from the Variable connector inside the Apply to each action.
2. Choose the TempArrayTotal variable previously initialized for the Name field. This will be a temporary running total of the array.
``add(items('Apply_to_each'),variables('ArrayTotal'))``
1. Add the above expression into the Value field for the Set variable action.

This expression will add the current item in the array to the ArrayTotal variable value. During the first iteration of the loop, the ArrayTotal variable is zero.

1. Add a second Set variable action inside the Apply to each action. This one should appear after the one added in step 2 as it will be used to store the new total.
2. Set the Name field to the ArrayTotal variable previously initialized.
3. Add the dynamic content reference to the TempArrayTotal variable in the Value field.

This setup will add the current array item value to the previous total during each iteration of the loop.

When the loop is finished the ArrayTotal variable will contain the sum of all items in the array.

Referencing the Sum Outside the Loop

Once the Apply to Each loop finishes executing, your flow will have calculated the sum of the array. The variable ArrayTotal will hold the total.

You can now use the ArrayTotal variable in other actions, such as saving the result to a data source or using it in other calculations.

Conclusions

In this post, you have learned how to sum an array in Power Automate using variables and loops.

By defining a variable to hold the total and iterating through the array items, you can increment the total variable for each array item processed.

Unfortunately, getting the sum from an array is not simple. If possible, it’s best to get the total from the source data and avoid potentially slow loops.

Have you needed to get the total from an array? How did you do it? Let me know in the comments?

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Subscribe

Subscribe for awesome tech videos 😃

Related Articles

How to Clear Out All Connectors in Power Automate

Do you want to clear out or remove unused connectors in Power Automate? Connectors are the building blocks for...

3 Ways to Sort an Excel Table in Power Automate

Do you need to sort your Excel table with Power Automate? Sorting data can help you quickly find trends, patterns, or...

How to Create an Excel Table in a Worksheet with Power Automate

Are you trying to create a new Excel table during your flow? Tables are the default when reading and writing data to...