Calculate more than 30 items in a single formula

[ccomins]

I have created a sharepoint list for work where I would like to get the SUM of 55 columns, however, it seems sharepoint online does not like formulas with more than 30 items. I have resorted to creating columns at the end of every section, calculating the sum of sections separately and then combining those total columns into a final total at the end. I would like to be able to do this with a single column/formula. Is this possible or is there a better way? 

I have looked into flow, but haven't figured out how to accomplish this there either.

I have searched but haven't found any info pertaining to this issue. I cant imagine I am the only one trying to calculate 30+ columns.

Thank you in advance.

[#2]

An alternative approach would be to create a PowerApps form and do the calculations in the form and save it back to the list

[#3]

@Dean Gross Thank you for your response. This list is actually populated from a PowerApp. The issue with running the calculation in the App is our operations dept wants to be able to edit then data in the SharePoint list directly, which would then need to adjust the calculation. 

[#4]

How about giving the ops dept an edit form in the app that is tailored for their needs, this would keep all of the business logic in one place and therefore provide more accuracy.

[#5]

@ccomins 

Maybe this helps: https://youtu.be/5GVYio9NKkw

I haven't tried it with the amount of columns you're talking about. 

I'm curious if there is a limit. 

Kind Regards

[#6]

 I will certainly keep that in mind. The app is still being developed, once that is complete I will look at implementing an edit form just for Ops. In the mean time I will just create a separate view for ops in the sharepoint list that will hide all the muck.

[#7]

@SmetsDavy Thank you for your response. I have seen that video before and tested the process. As far as I can tell the add expression in flow can only add TWO items at once. I am new when it comes to flow, so if i am incorrect on that, please let me know. I would love to use flow to calculate the total columns, I just haven't found a way.

[#8]

I have hit the wall several times. They at least could tell you when it errors out. No, you lose all your data and keep fighting. @ccomins 

[#9]

@rpotter @ccomins Yes, SUM function takes up to 30 columns/arguments only. This is by design limitation in SharePoint formulas.

So, you can use the nested SUM functions or the SUM function with addition ( + ) like: 

=SUM(SUM([Col],[Col],[Co3]),SUM([Co31],[Co32],[Co33]))

In 1st nested SUM() you can use [Col1] to [Col30] and 2nd nested SUM() you can use [Col31] to [Col55].

OR 

=SUM([Col],[Col],[Co3])+SUM([Co31],[Co32],[Co33])

In 1st SUM() you can use [Col1] to [Col30] and 2nd SUM() you can use [Col31] to [Col55]. 

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

[#10]

Thank you this helps! @ganeshsanap