Counting completed items, only if relevant
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?
Best Answer

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)))))
Answers

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)))))

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)))))

Thanks David, much appreciated

No problem, glad it works.
Help Article Resources
Categories
Check out the Formula Handbook template!