Formula help

SharonR
SharonR ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

Hello, 

I have been trying to work this one out.  Attached is a picture of the column that i wish to tally.  This column is a checkbox column and there are multiple parent lines (these are the colours lines) The top line is the grandparent line the white rows are the children.

 

So what you see is each children row has some boxes check or not depending on completion of task for that provider, each provider is categorised into sectors.  I have used the following formula to get a total for each parent line ="" + COUNTIF(CHILDREN(), 1).

 

I need a formula that will add/count the parent lines and hopefully have the cell read "Total signed agreements: ??".  I have a feeling it has to do with my previous formula is the reason for my problem.

It should be a simple "Total signed Agreement: " + SUM(PARENT)

 

Help

 

Solution.PNG

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 10/23/18

    Hi Sharon,

    The formula must be in a separate column for it to work. If it's important to see the text in the checkbox column you can do it with a helper column.

    Try this.

    Count (Helper Column - Text/Number)

    =COUNTIF(CHILDREN(Signed@row); 1)  (Placed in the parent cell of each section in the count column)

    Signed (Checkbox column)

    ="Total: " + COUNTIF(CHILDREN(); 1) (Placed in the parent cell of each section)

    ="Total signed agreements: " + SUM(CHILDREN(Count@row)) (Placed in the top cell or where you like, to show the total signed)

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Would that work for you?

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!