Counting Checkboxes of all Parent rows in a sheet

We have a sheet that has a multiple level hierarchy. One of the columns is a checkbox and I used the formula ="" + COUNTIF(CHILDREN(), 1) to count the number of checkboxes below each parent row. This created the number of checked box in the parent row. Is there then a way to count all the numbers in the parent rows (to get a total amount of checkboxes in all hierarchies in the sheet)? I have tried several formulas, but none have worked. Thanks in advance!

P.S. The SUM formula alone does not work.

Find more posts tagged with

Formulas

Comments

Christine Oakes

Lindsey

Probably the easiest way to do this would be to create a Row at the top of your sheet that is parent to all rows below. You can then use =Sum(Children()).

Christine

LindseyC

I have tried this and it comes up blank. (not an error, but a blank cell)

Also, I have already have a hierarchy that is three levels so when adding an addition level, it hides the info in the last level.

Thank you!

Atus Bartal

Lindsey,

By using ""+ in the formula you create a text (I know, otherwise it would come up with an error message in the checkbox-type column), that's why you cannot sum the "numbers" in the parent rows. You could convert the text value to numeric format with the VALUE() formula, but only in a column with text/number format. I would make all these calculations in another column.

Atus

LindseyC

Hi Atus,

Thank you! I thought it was probably the ""+ causing the issue. I added a column, used the VALUE() formula, used the SUM() formula to add the total to the top of my sheet and then hid the additional column. It works show the information I needed. I very much appreciate your help!

Lindsey