Smartsheet "SUM" Formula not Working
I am trying to add the numbers from several columns in a Smartsheet. I am using this formula:
=SUM([CALCNumber of Affected Applications]@row + [CALCNew Process or Procedure]@row + [CALCNew Software?]@row + [CALCNew or Edited Reports]@row + [CALCNumber of IT Resources Needed]@row + [CALCNumber of Departments Involved]@row + [CALCEstimated Time to Complete]@row + [CALCAmount of Revenue Potential]@row + [CALCNumber of Known Risks]@row + [CALCNumber of Known Benefits]@row + [CALCCost]@row)
However, although there are numbers in every column, I am getting a result of 0. What am I missing?
Also, the columns I am wanting to sum have formulas in them. But I need to add the value that the formula creates.
Answers

I hope you're well and safe!
Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.
Be safe, and have a fantastic week!
Best,
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.



Take out the plus signs and separate the column references with commas.
=SUM([sum 2 and 4]@row, [sum 4 and 6]@row, [sum 1 and 6]@row)

=SUM([CALCNumber of Affected Applications]@row, [CALCNew Process or Procedure]@row, [CALCNew Software?]@row, [CALCNew or Edited Reports]@row, [CALCNumber of IT Resources Needed]@row, [CALCNumber of Departments Involved]@row, [CALCEstimated Time to Complete]@row, [CALCAmount of Revenue Potential]@row, [CALCNumber of Known Risks]@row, [CALCNumber of Known Benefits]@row, [CALCCost]@row)

@Austin Smith Thank you. However, it returns an #UNPARSEABLE

It shouldn't matter if there are words in any of the fields (they should just be ignored but the sum fx), but without visible data, it's hard to tell why you're getting an unparseable error, especially since you have so many formulas hiding in there.
1) (just checking off basics) are you 100% certain that all of the other formulas work 100% of the time?
2) What happens when you try:
=IFERROR(SUM([CALCNumber of Affected Applications]@row, [CALCNew Process or Procedure]@row, [CALCNew Software?]@row, [CALCNew or Edited Reports]@row, [CALCNumber of IT Resources Needed]@row, [CALCNumber of Departments Involved]@row, [CALCEstimated Time to Complete]@row, [CALCAmount of Revenue Potential]@row, [CALCNumber of Known Risks]@row, [CALCNumber of Known Benefits]@row, [CALCCost]@row),"")

What are the formulas used to populate the individual cells?

Here is the formula that I am trying to get to work. It doesn't give an error, only comes back with a 0:
=SUM([CALCNumber of Known Benefits]@row, [CALCNumber of Known Risks]@row, [CALCNumber of Affected Applications]@row, [CALCNew Process or Procedure]@row, [CALCNew Software]@row, [CALCNew or Edited Reports]@row, [CALCNumber of Known Resources Needed]@row, [CALCMet with Business Areas]@row, [CALCNumber of Departments Involved in Project]@row, [CALCEstimated Time to Complete]@row, [CALCAmount of Revenue Potential]@row, [CALCIs the Project Funded?]@row, [CALCProject Funded Amount]@row)/12
@Paul Newcome They are all different. But here is an example:
=IF([Estimated Time to Complete]@row = "0 to 3 weeks", "1", IF([Estimated Time to Complete]@row = "4 to 6 weeks", "2", IF([Estimated Time to Complete]@row = "7 to 10 weeks", "3", IF([Estimated Time to Complete]@row = "10 to 12 weeks", "4", "5"))))
All formulas in the other columns have been verified to give accurate results.

I figured it out. The "" around the desired value made it a text value rather than a number value. I've removed the "" from all formulas and now the SUM formula is working.


@Katherine Simpson Yes. That's what I was thinking was going to be it. Glad you got it sorted.
Help Article Resources
Categories
Check out the Formula Handbook template!