Sum the number of Grandchildren check boxes

CDub

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? 

 

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.