Smartsheet "SUM" Formula not Working

Katherine Simpson

I am trying to add the numbers from several columns in a Smartsheet. I am using this formula:

=SUM([CALC-Number of Affected Applications]@row + [CALC-New Process or Procedure]@row + [CALC-New Software?]@row + [CALC-New or Edited Reports]@row + [CALC-Number of IT Resources Needed]@row + [CALC-Number of Departments Involved]@row + [CALC-Estimated Time to Complete]@row + [CALC-Amount of Revenue Potential]@row + [CALC-Number of Known Risks]@row + [CALC-Number of Known Benefits]@row + [CALC-Cost]@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.

Andrée Starå

Hi @Katherine Simpson

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!

Katherine Simpson

@Andrée Starå

Thank you for responding. I have taken screenshots and added some clarity.

Katherine Simpson

@Andrée Starå

Hey Andree,

Any help you can provide would be great. Did the screenshots help?

Austin Smith

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)

Austin Smith

=SUM([CALC-Number of Affected Applications]@row, [CALC-New Process or Procedure]@row, [CALC-New Software?]@row, [CALC-New or Edited Reports]@row, [CALC-Number of IT Resources Needed]@row, [CALC-Number of Departments Involved]@row, [CALC-Estimated Time to Complete]@row, [CALC-Amount of Revenue Potential]@row, [CALC-Number of Known Risks]@row, [CALC-Number of Known Benefits]@row, [CALC-Cost]@row)

Katherine Simpson

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

Austin Smith

@Katherine Simpson

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([CALC-Number of Affected Applications]@row, [CALC-New Process or Procedure]@row, [CALC-New Software?]@row, [CALC-New or Edited Reports]@row, [CALC-Number of IT Resources Needed]@row, [CALC-Number of Departments Involved]@row, [CALC-Estimated Time to Complete]@row, [CALC-Amount of Revenue Potential]@row, [CALC-Number of Known Risks]@row, [CALC-Number of Known Benefits]@row, [CALC-Cost]@row),"-")

Paul Newcome

What are the formulas used to populate the individual cells?

Katherine Simpson

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([CALC-Number of Known Benefits]@row, [CALC-Number of Known Risks]@row, [CALC-Number of Affected Applications]@row, [CALC-New Process or Procedure]@row, [CALC-New Software]@row, [CALC-New or Edited Reports]@row, [CALC-Number of Known Resources Needed]@row, [CALC-Met with Business Areas]@row, [CALC-Number of Departments Involved in Project]@row, [CALC-Estimated Time to Complete]@row, [CALC-Amount of Revenue Potential]@row, [CALC-Is the Project Funded?]@row, [CALC-Project 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.

Katherine Simpson

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.

Austin Smith

@Katherine Simpson

Great!

Paul Newcome

@Katherine Simpson Yes. That's what I was thinking was going to be it. Glad you got it sorted.