Sum of Children - not working
Hello All,
I'm trying to get the sum of children. There are 4 children rows, with multiple rows beneath that. See image.
In this case, the top row with cell value 0 would have a 2 because it's summing the purple 0 and purple 2. Purple 0 and Purple 2 are counting the cells below (children) that have checkboxes.
I'm using =COUNTIF(CHILDREN(), 1) + ""Β which works well for the count of checkboxes. But I can't get the top row to sum the two children.
Cheers,
Β
Comments
-
Here's another version of the issue, but simplified....
Β
-
Ok,
I had to rethink my process. I couldn't get it to work.
This formula and layout works.
Brown= Formula Cell
Β
I'm creating a 1 value if two conditions are met, then counting them in the parent. Then summing summing them in the grand-parent.
Β
-
Just
=sum(children())
should work fine. if that doesn't work you have a data type issue which seems weird to me coming out of a countif. That said one of the sum's you have has an incorrect comma in it which might be causing your issue.
-
=sum(children()) didn't work. I wish it did.
-
Hi Craig,
You can't sum the numbers in a checkbox column when you've converted it to text with the +"". It has to be converted back to number for it to work. I'd recommend using a helper column for the calculations and then show the result in the checkbox column.
Would that work?
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: 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.
-
Hi Andree,
I tried the +"" and it didn't work. Do you see the screenshot above? I copied the formulas there too.
Β
I get an #UNPARSEABLE error
-
What I meant was that if you're using the +"" trick. The values won't be numbers anymore. They'll be text and need to be converted back to numbers again for the calculations to work.
Make sense?
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.
-
Further explanation...
Β
The formula that you are using to generate the numbers in the checkbox column that ends in + "" is converting the number into a text. since text cannot be added, you will need to use a "Helper Column" to convert these values back into numbers then you can use the numerical values in your helper column within the SUM function.
Β
In the Helper column you would use
Β
=VALUE([Checkbox Column Name]@row)
Β
.
To get your sum, you would now use
Β
=SUM(CHILDREN([Helper Column]@row))
Help Article Resources
Categories
Check out the Formula Handbook template!