Grouping and Calculate Totals from One SharePoint List to Another

You might be wondering how to summarize list items by grouping and summing them up together to another SharePoint List? Well it’s quite simple and you can achieve them just by using workflows created in SharePoint Designer.

While workflows does not support aggregation of data, we can create some smart calculation similar to running totals. To achieve that, we just need to store past values of what you want to calculate so we can compare them with the new ones and in return calculate the difference and apply it with the running total. Sounds pretty straightforward? So let’s do it in real scenario.

Let’s assume you have a list called Invoice Detail which contains line item details of an invoice like Description, Amount and Invoice Number. We also need to store the Old Amount so you need a column for that as well.

Then you have a summary list called Invoice Summary where you group everything by Invoice Number and sum the Amounts then store it in Total Amount.

So let’s create a workflow that will trigger on update and create a list item in your Invoice Detail. First step is we need to create a new Summary Item but we need to verify if that exists first by searching for the created or updated item’s Invoice Number in your Invoice Summary List.

If it returns a value, it means that the Invoice Number already exists on the Invoice Summary List hence we just need to update them with the new total.  First is we need to calculate the difference of the Old and New Amount values then store it in a local variable called TotalChangedAmount, this step means that we will only apply the changed amount to the running total.

Then we need to add that calculated amount to the Total Amount stored in the Invoice Summary List and save it.

And if Invoice Number does not exist, then it means we need to create them.

The final step is then store your Old Amount so you can use them for calculation later.

So bringing them all together should look like this:

Now you can try it and see for yourself.