• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Tachytelic.net

  • Get in Touch
  • About Me

How to Sum an Array of Numbers in Power Automate

March 23, 2021 by Paulie Leave a Comment

Although it sounds like a very simple thing to do, summing an array of numbers in Power Automate can be quite difficult if you have a large list of numbers to sum.

The most common method is to use an “apply each” action to loop round an array of numbers and increment the value of a variable. Here is a basic example of this:

Image of example flow used to sum an array of numbers.

This method is completely valid, and works just fine if you are only summing a small list of numbers. The problem is that there is very little scope for performance improvement. I tested the flow above with 1,000 numbers and it took 10 minutes to execute, which is unacceptably slow.

Finding a better way

Fellow Power Automate lover Damien Bird and I were trying to come up with a better solution for this and he created a neat method to sum an array which he posted on his blog, which solves the performance issue.

I have come up with another method, which is both fast and works for integers, floats and negative numbers.

Fast and Simple Solution to Sum an Array of Numbers in Power Automate

My solution to this actually turned out to be very simple. It works by harnessing the Power of Typescript in Excel online to perform the sum calculation on behalf of Power Automate.

In terms of performance I tested it with a list of 20,000 numbers ranging in size from -500,000 to positive 500,000 and it took 5 seconds to complete, here is how it is done:

Create an Excel Workbook to Store Functions

The first step is to head into Excel online and create a new Workbook called Power-Automate-Functions. Once you are in the workbook do the following:

  • Click on the Automate Tab
  • Click All Scripts
  • Click New Script

In the script window paste in the following code:

function main(
  workbook: ExcelScript.Workbook,
  numbersToSum: Array<number> =[],
  ) {

  let sum = numbersToSum.reduce((a, b) => a + b, 0);
  return sum
}

Rename it to SumArray, it should look like this:

Image of Excel online Office Scripts Editor creating a function to sum values.

The simple script accepts an array parameter called numbersToSum and has a single return value of sum.

Once you have saved the script, the Power Automate Flow is simple.

Execute the Excel function from the Power Automate Flow

Execution of this function is very easy, here is an example flow:

Image of a Power Automate Flow used to sum an array of Numbers

Simply choose the “Run Script” action from Excel online and provide your number array for the numbersToSum parameter. When you execute the flow you can easily see the return value in the output:

Image of the Output of a Flow

That is all there is to it! As I said above, I was able to process an array of 20,000 numbers in 5 seconds, so quite happy with the performance.

Conclusion

I’ve ignored the scripting functionality in Excel up until now. But I now realise that it gives Power Automate developer access to TypeScript which we can use to fill in lots of missing gaps in standard Power Automate functionality. I’m quite excited about it.

Being able to sum an array of values is just the first of many possiblities.

If you want to learn more about Office Scripts in Excel then check out this page from Microsoft.

Once again, thank you to Damien Bird for the inspiration for this idea.

Filed Under: Power Platform Tagged With: Power Automate

Reader Interactions

Leave a Reply Cancel reply

Primary Sidebar

Power Automate Support Image
Link to my LinkedIn Profile

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 107 other subscribers.

Go to mobile version