Sum the number of Grandchildren check boxes

edited 12/09/19 in Formulas and Functions
12/20/18 Edited 12/09/19

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

Popular Tags:

Comments

  • Andrée Starå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

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    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 about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul NewcomePaul 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.

    thinkspi.com

Sign In or Register to comment.