# 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.

• ✭✭✭✭✭✭

Try this one:

```=IF([Total Rating]@row > 9, "Red", IF([Total Rating]@row > 5, "Yellow", "Green"))
```

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

«1

• ✭✭✭✭✭✭

What is the SUM function you're using?

Try this in the Total Rating column of the parent rows.

Regards,

Jeff Reisman

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• edited 02/26/20

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?

• ✭✭✭✭✭✭
edited 02/26/20

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

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

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭

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.

• ✭✭✭✭✭✭
edited 02/26/20

Aha - good catch Andree! It's hard to do math with text values!

Regards,

Jeff Reisman

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

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!

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!