Sum the number of Grandchildren check boxes

Hi all!

I'm trying to figure out the formula for how to sum the number of check boxes from the parents. I have the children figured out but it is the summing of the parents that isn't working.

(See Picture) I would expect the Retail line to say 3 instead of zero. The formula that I thought would work is ="" + SUM(CHILDREN()) but that isn't work. Any suggestions for what else to use?

Smartsheet Picture.png

Find more posts tagged with

Formulas

Comments

Andrée Starå

Hi,

The checkbox column is expecting a boolean value of 0 or 1, so usually, a formula wouldn't work, but because you've added the + "" it does but the result isn't a number, it's text.

To be able to calculate the sum of that text it needs to be converted to numbers again with the VALUE() function, and you have to do that in another column.

Many times I find it easier to add a helper column where everything is calculated and then just link the cells to the checkbox column where needed (remember to add + "")

Would that work?

Have a fantastic day & Happy Holidays!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

I did some testing with Andree's suggestion. I found that using helper columns is going to be the only way to do it while using the CHILDREN function. To use a number that is displayed as text within a calculation, you need to wrap it in a VALUE function. Unfortunately VALUE(CHILDREN()) is invalid. Using a text/number type helper column where you can have actual values displayed as opposed to text is the only way to go that I could find if you wanted to continue using the CHILDREN function.

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…