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

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