Sum Function Error

@JReisman27 & @Nathan Nguyen,

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?

Try this one:

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

All comments

What is the SUM function you're using?

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

=SUM(CHILDREN([Workload Rating]@row))

@JReisman27

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))

@JReisman27

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!

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

@Nathan Nguyen

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?

@Nathan Nguyen,

Happy to help!

Did you see this one?

=SUM(CHILDREN([Workload Rating]@row))

Did that work?

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

@JReisman27, I missed that you already answered/tested that.

Strange!

@Nathan Nguyen, Do you get an error message?

@JReisman27 & @Nathan Nguyen,

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?

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

@JReisman27 @Andrée Starå

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"))