How to Sum an Array of Numbers in Power Automate

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:

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:

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:

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:

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.