Sum Function Error
I created a resource planning sheet to track each employee's workload. I have Workload Rating automatically changed to 3, 2, or 1 based on the Engagement Level. Lead = 3, Core = 2, and Ad Hoc = 1. I also have a Total Rating to sum up the Workload Rating. However, it returns a 0, for example, for Employee 1, the Total Rating should be 6. Workload Rating column have multiple IF functions, so I'm thinking that might throw the sum off for some reason. Any help would be appreciated. Thanks.
Best Answers

I see now why it's not working! It's because the formula in the cells as jreisman mentioned. They are interpreted as text because they are surrounded by "". Try removing the "".
Did that work?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Try this one:
=IF([Total Rating]@row > 9, "Red", IF([Total Rating]@row > 5, "Yellow", "Green"))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers

What is the SUM function you're using?
Try this in the Total Rating column of the parent rows.
=SUM(CHILDREN([Workload Rating]@row))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

I'm using this formula. I want the Total Rating to capture the cells from the Workload Rating. I tried your formula, but didn't work.

That's very strange. It works just fine on my test sheet. Even when I make my column4 values formulas adding together other fields, it still adds up using =SUM(CHILDREN([Column4]@row))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

As mentioned, I think I have formulas in the Workload Rating column, which might caused the sheet to not recognize the numbers to sum up. Did you type in numbers in your test?

Hi @Nathan Nguyen,
Try something like this.
=SUM([Workload Rating]:[Workload Rating])
Or this.
=SUM(CHILDREN([Workload Rating]@row))
Did that work?
I hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. 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:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

@Andrée Starå Thanks for your input. Unfortunately, it still didn't work. The number still return 0. Also, I don't want to sum up the whole column, just the rows that are pertaining to that employee. Each employee will have their own total.

It looks like you're using formulas in a drop down column. If you are setting the value via formula, why have the drop down? Try setting the column type back to text/number if you are setting the value by formula. Don't mix formulas into a drop down column and vice versa
My formula works whether I type numbers into my fields, or if my fields are set by formula.
My assumption is that you just want to add up the child rows for each parent row, correct?
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Happy to help!
Did you see this one?
=SUM(CHILDREN([Workload Rating]@row))
Did that work?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

@Andrée Starå I gave him that formula from the start. I think having formulas mixed with a drop down column are causing issues. There's no reason that formula would not work work whether the data is being typed in or generated via formula.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

@JReisman27, I missed that you already answered/tested that.
Strange!
@Nathan Nguyen, Do you get an error message?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

I see now why it's not working! It's because the formula in the cells as jreisman mentioned. They are interpreted as text because they are surrounded by "". Try removing the "".
Did that work?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Aha  good catch Andree! It's hard to do math with text values!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Thank you both for your help. I removed the quotation marks in the Workload Rating like Andree said and it worked. I used the formula that you provided JReisman
On a separate note, would any of you know the best formula I can use for the status color. I was thinking any below 4 is green, 5  8 is yellow, and greater than 9 is red. Thanks!

Try this one:
=IF([Total Rating]@row > 9, "Red", IF([Total Rating]@row > 5, "Yellow", "Green"))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Excellent!
Happy to help!
I saw that already got the new question answered.
Let me know if I can help with anything else!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
Check out the Formula Handbook template!