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

Tracey Siepser

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

J. Craig Williams

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

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