Counting completed items, only if relevant

Neil Watson

I have rows of items. Each row starts with 2 columns - Done and Not relevant.

When someone actions a row, they tick off the checkbox under "Done".

If the row is not applicable, they tick off the checkbox under "Not relevant".

The rows are children of a Header row which counts the rows completed using the formula ="Completed: " + COUNTIF(CHILDREN(Done7), 1) + " of " + ((COUNTIF(CHILDREN(Done7), 1) + (COUNTIF(CHILDREN(Done7), 0))))

I want to exclude from this count the rows that are ticked as "Not relevant"

Is this possible?

David Tutwiler

Sorry, something is going weird with the Post interface and it wouldn't let me edit. The correct formula should be this (the first Not relevant is missing the 7):

="Completed: " + COUNTIFS(CHILDREN(Done7), 1, CHILDREN([Not relevant]7),0) + " of " + ((COUNTIFS(CHILDREN(Done7), 1, CHILDREN([Not relevant]7), 0)) + (COUNTIFS(CHILDREN(Done7), 0, CHILDREN([Not relevant]7),0)))))

David Tutwiler

I think you would just need to make your COUNTIF into COUNTIFS. It would look like:

="Completed: " + COUNTIFS(CHILDREN(Done@row), 1, CHILDREN([Not relevant]),0) + " of " + ((COUNTIFS(CHILDREN(Done7), 1, CHILDREN([Not relevant]7), 0)) + (COUNTIFS(CHILDREN(Done7), 0, CHILDREN([Not relevant]7),0)))))

David Tutwiler

Sorry, something is going weird with the Post interface and it wouldn't let me edit. The correct formula should be this (the first Not relevant is missing the 7):

="Completed: " + COUNTIFS(CHILDREN(Done7), 1, CHILDREN([Not relevant]7),0) + " of " + ((COUNTIFS(CHILDREN(Done7), 1, CHILDREN([Not relevant]7), 0)) + (COUNTIFS(CHILDREN(Done7), 0, CHILDREN([Not relevant]7),0)))))

Neil Watson

Thanks David, much appreciated

David Tutwiler

No problem, glad it works.