Checkbox rollup based on another column
I am trying to create a formula that rolls up an action item checkbox if the action item column in the same row is not null.
I found a found this in a different post - =ROUND(IF(COUNT(CHILDREN()) > 0, COUNTIF(CHILDREN(), 1) / (COUNT(CHILDREN()) - COUNTIF(CHILDREN(), "N/A")), 0) * 100, 0) + "%"
It checks if the actual column has n/a, I want to modify it to check that the next column in blank. For the purpose of this forumla, I have 2 columns [Action Completed?] and [Action Item], I also have meeting date headers. Goal is to have the meeting date headers roll up the Action Items checkbox, what I posted above rolls it up, but it should exclude where the Action Item field is blank.
I was trying to get it to work where Action Item = 1 as a simple test case before I figured out how to null check it, I was trying to use the second COUNTIF() like below but kept getting #UNPARSEABLE, not sure what I was missing.
=ROUND(IF(COUNT(CHILDREN()) > 0, COUNTIF(CHILDREN(), 1) / (COUNT(CHILDREN()) - COUNTIF(CHILDREN([Action Item]), 1)), 0) * 100, 0) + "%"
also tried
=ROUND(IF(COUNT(CHILDREN()) > 0, COUNTIF(CHILDREN(), 1) / (COUNT(CHILDREN()) - COUNTIF(CHILDREN(), [Action Item] = 1)), 0) * 100, 0) + "%"
Any help is appreciated.
Answers
-
Hey @mgupta
Would you be able to post a screen capture of your sheet? It might help to visualize what you're attempting to accomplish (but block out sensitive data).
I will note that in your first formula it looks like you're just missing the @row function to look at the Action Item cell in this row.
Let me know if this does the trick:
=ROUND(IF(COUNT(CHILDREN()) > 0, COUNTIF(CHILDREN(), 1) / (COUNT(CHILDREN()) - COUNTIF(CHILDREN([Action Item]@row), 1)), 0) * 100, 0) + "%"
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi Genevieve,
Thanks for your reply, I am posting a screenshot below with the sensitive data collpased to its relevant groups.
Looking at the grouping under 12/20 - since there is nothing in Action Item field, I want the rollup to notice that and not count it if that makes sense.
I tried entering the formula as you shared it but I dont think it did it, I will try messing with the @row function however, thanks for showing that!
-
Hi @mgupta
Thanks for clarifying and for posting a screen capture, that definitely helps!
Since you want to ignore the blank children under the Action Item column, we'll need to add that into other areas of your formula as well:
=IF(COUNT(CHILDREN([Action Item]@row)) < 1, "-", ROUND(COUNTIF(CHILDREN(), 1) / COUNT(CHILDREN([Action Item]@row)) * 100) + "%")
This first checks to see if there are any children with text in the Action Item column. (If not, it returns - in the cell). Then it counts how many children in the current column have a checked box, and divides that by the total number of children with text in the Action Item column.
Let me know if that works 🙂
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!