Forum Discussion
Sum for a Calculated Column
I am tracking invoices for projects throughout the year. The columns in my SharePoint list include:
- PROJECT TITLE
- JAN, FEB, MAR… DEC (monthly invoice amounts, formatted as currency)
- TOTAL (a calculated column that sums the monthly values)
The issue I’m facing is that I can see the Sum for each month at the bottom of the view, but I cannot see the Sum for the TOTAL column. Since it’s a calculated field, SharePoint won’t let me display its sum.
If anyone knows how to work around this, I’d be incredibly grateful!
2 Replies
I would suggest on of the three options below:
- Instead of a Calculated column, use a Currency column and use Power Automate flow to manage updating the total for each row. Since an actual value is added to the Total column, it will show the Sum correctly in the footer. The trick here would be when to run the flow (only trigger when one of the month values has been updated) to ensure you don't end up with an infinite loop.
- What I would likely do myself is just use an Excel Table to manage everything which will be able to show all the Totals, Grand Totals, etc. using formulas. You can also lock down certain cells such as Total so the formula can't be overwritten. Is there a reason you have it as a SharePoint list?
- Use a Table in Dataverse (and maybe a Model Driven App) that will allow you to have calculated totals for each row, and grand total across rows.
With SharePoint on its own this isn't possible, but there is a workaround using Power Automate that will put the sum from the monthly total columns (i.e the sum of the total column) in a new column on the item with the maximum ID. It doesn't require you to loop through all the items so it drastically reduces the number of api calls if you have a large list.
Let me know if you want details of the flow.
Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)
