Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Formula to calculate count of checkboxes in multiple columns and child rows

Tracey Siepser
Tracey Siepser ✭✭✭✭✭✭
edited 12/09/19 in Archived 2015 Posts

Hi Everyone,

 

I'm trying to write a formula that adds up the number of unchecked child rows in the Complete column and then subtracts the number of checked rows from the Non Applicable column. The result should be a number of tasks left to be completed from within that parent and child section. I've tried a bunch of different formulas but no luck so far. Any ideas on what I'm doing wrong? I could use this formula in many kinds of sheet applications but haven't been able to crack it yet.

 

 

Thank you!

-Tracey

11-23-2015 6-22-57 PM.jpg

Comments

  • J. Craig Williams
    J. Craig Williams Top Contributor
    edited 11/24/15

    Tracey,

     

    One of the problems you may be running into is that the Not Applicable count may include items that are completed.

    That is, if you have 10 items, 4 of which are marked complete and 2 of those are not applicable but also 2 not complete ones are also not complete, you want the not finished count to be 4, not 2 (6 not complete - 4 not applicable)

    [also your example is checking for Not Complete (complete = false) and Applicable (not applicable =  false)]

     

    2 complete, not applicable

    2 complete, applicable

    2 not complete, not applicable

    4 not complete, applicable

     

    You may also be running into CHILDREN() not working with COUNTIF. The doc says it does but I'm having problems with it today.

    I was hoping to use COUNTIFS instead ... but doc says CHILDREN don't work with that function.

     

    So, here's a work around.

    1. Create a new column (text/number) that is Not_Complete_and_Applicable

    (I use _ because I dislike spaces when coding).

    2. The formula in row 21 would be:

    =IF(AND(NOT(Complete21), NOT([Not Applicable]21)), 1)

    that is 

    if not complete and applicable, value is 1 otherwise blank/nothing.

    3. in the parent row, this formula gives the not finished ones:

    =COUNT(CHILDREN())

    which you could reference in your "Not finished: " text,

     

    I'm missing something with CHILDREN and need to do a little more digging. It seems to be rejecting Booleans (check boxes) and I'm not sure why.

    But until then, the above should get you want you need for now.

     

    I hope that helps.

     

    Craig

     

  • Tracey Siepser
    Tracey Siepser ✭✭✭✭✭✭

    Hi Craig,

     

    Thank you!! Your thorough response was an incredble help. I have the sheet displaying totals like a charm now after some work updating it and adding hidden columns.

    I have to be very careful adding rows in the future since each require the "=IF(AND(NOT(Complete3), NOT([Not Applicable]3)), 1)" formula in each of the child rows (in a hidden column) instead of calculating them from the parent rows. That one snag would be nice to elliminate but this definitely works and will be incredibly useful while my staff is in these new checklists.

     

    Here's a screenshot of the collapsed view showing the total tasks left to complete and the amount left in each section.

     

    Thanks again!! 

    -Tracey

    11-25-2015 11-49-42 AM.jpg

This discussion has been closed.