Checkbox rollup based on another column

mgupta

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.

Genevieve P.

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

mgupta

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!

Genevieve P.

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