It might suprise you to hear that there is not a SUM function in Power Automate, if you wish to add a list of numbers you must use a variable to store the running total of a sum via apply to each using the add expression and an update variable action. For large lists the running time is pretty significant. I explored how to add positive numbers together as efficiently as possible using the current expressions and actions available and came up with a slightly complex single select action to do this. During testing I added over 600 numbers from 0 – 100 and the action completed in 0 seconds. In order to perform a sum on negative and positive numbers, you could consider filtering the list into two lists (positive and negative), add them both up and subtract the sum of the negative from the positive. Please note you would have to remove the minus (-) from the values in the negative list in order for this to work.
So how does this work and what does the solution look like?
Let us explore the magic of the Select Action
First off, the input for the select action takes the output from the list of comma seperated numbers in the compose and splits them into an array. Ultimately you want to pass this select an array of numbers from any data source of your choice.
split(outputs('MyNumbers'),',')Then for the mapping I use a complex formula using several expressions, which will all become clear below.
substring(join(range(0,add(int(item()),1)),''),0,int(item()))substring( [create join on a range of numbers from 0 to integer in item() ] , 0 , [length of substring is integer of item()] )
join( [joins the range of numbers from 0 to integer in item()] , [with an empty string ”] )
range( [from 0] , [to the item number + 1 in order to accept 0 as a number as range cannot be 0 to 0] )
The result of the above expression is a string that is the same length as the numbers of the list added together. I have deliberately created a string longer than the each number and then reduced the string to the length of the original number using the substring, but why would I create a long string??
The simple reason being, the only expression available to Power Automate that can return the sum of a value is “length“. This expression will immediately return the length of a string in 0 seconds and I have tested this on lengths over 30,000, which means you can add lists of numbers to 30,000 and potentially beyond.
length(join(body('TheMagic'),''))Ok, it’s a hack, an interesting method for calculating the sum of positive numbers and could be developed to calculate the sum of negative and positive ints. In theory it could calculate floats too. Decide on your decimal points and multiply by 10s, 100s, 1000s in order to shift that decimal place. After you have performed the calculation using your select, divide the answer by the original multiplier in order to calculate your float. This might be useful when dealing with monetary values and simply multiple and divide by 100.
What do you think? Could you use this somewhere? How long will it be until Power Automate introduce the sum of values? Please comment below and get in touch if you have any thoughts.
If you found that interesting, why don’t you look at my article of Union, Except and Intercept, all without an Apply to Each.
**Update** Just 24 hours later, Paul Murana and I had been working on finding a solution to this age old problem of summing an Array of numbers in PowerAutomate and whilst my option is viable, Paul has come up with an absolute gem of a solution which also opens up many more options with Typescript. Please note that his solution is in preview and is limited to 200 runs per user per day. Like anything, consider all avenues and what is right for your solution. Read more here.
So smart!
Really Magician
Good day! Another alternative is to use the XPath “sum(…)” function, which seems to be even faster.
So, for your array:
split(outputs(‘MyNumbers’),’,’)
tl;dr
Use this complete expression:
xpath(xml(json(concat(‘{“x”:{“n”:’, split(outputs(‘MyNumbers’),’,’),’}}’))),’sum(//n[number(.)=number(.)])’)
Here are the steps:
1) Since an array cannot be converted directly to XML, you have to first convert it to JSON from a string:
concat(‘{“x”:{“n”:’, split(outputs(‘MyNumbers’),’,’),’}}’)
2) This produces a JSON object like this:
{
“x”: {
“n”: [0 … N] <— your array
}
}
3) Then you need to convert it to an actual JSON object since it's a string:
json(concat('{"x":{"n":', split(outputs('MyNumbers'),','),'}}'))
4) Next, you convert the JSON to XML:
xml(json(concat('{"x":{"n":', split(outputs('MyNumbers'),','),'}}')))
5) Lastly, you apply the XPath expression:
sum(//n[number(.)=number(.)])
This XPath looks at the node "n" (//n) and sums all of the numbers within that node.
So the complete expression is:
xpath(xml(json(concat('{"x":{"n":', split(outputs('MyNumbers'),','),'}}'))),'sum(//n[number(.)=number(.)])')
I forgot to mention, this will also handle decimals and negative numbers.
Absolutely Joey, I’ve recently seen this idea covered by Paul Murana too https://www.tachytelic.net/2021/06/power-automate-instant-sum-array. Great to learn a new way. Hopefully though this article is still of use as it covers other concepts too.
How do I get the sum of certain rows in Excel via Power Automate?
This is quite an old blog post to be honest and I would now sum values using xpath as it can be done relatively easily. You would need to filter your excel sheet, before summing them. Always a question to ask on the forum if you are not sure.
This is so clever! Had a dataset that simply would not play nice with the XML method & this saved the day!