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!