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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!