Trying to sum range of children based on checkboxes

tgattsh

Hi everyone! Hope someone can help.

I am trying to total children in a parent row based on the checkboxes @ row.

Been at this for hours and have tried many different things, the last was "=SUMIFS(CHILDREN([Number of Affected Meters]3:[Number of Affected Meters]4, CHILDREN([Sales or MTS Notified Customer]3:[Sales or MTS Notified Customer]4,1))"

Any help would be much appreciated. Thank you...

Paul Newcome

We need to convert it into a text value since you are putting it in a checkbox column:

=SUMIFS(CHILDREN([Number of Affected Meters]@row), CHILDREN(),1) + ""

tgattsh

That worked. You're Awesome!

Paul Newcome

You would use my very first formula where we specify both columns within the CHILDREN functions or the SUM(CHILDREN()) you mentioned.

Then in the checkbox column parent rows you would use

=[helper 1]@row + ""

Paul Newcome

Try this:

=SUMIFS(CHILDREN([Number of Affected Meters]@row), CHILDREN([Sales or MTS Notified Customer]@row),1)

tgattsh

Thanks for your response @Paul Newcome but that resulted in a #Boolean Expected.

tgattsh

What I am trying to achieve is if the check box to the right of "12" is checked and the checkbox to the right of "3355" is checked, the parent row above the checkboxes would total 3367.

Paul Newcome

We need to convert it into a text value since you are putting it in a checkbox column:

=SUMIFS(CHILDREN([Number of Affected Meters]@row), CHILDREN(),1) + ""

tgattsh

That worked. You're Awesome!

tgattsh

@Paul Newcome I tried the same thing =SUM(CHILDREN()) + "" for the "BLUE" parent row but seem to be missing something. Any ideas?

Paul Newcome

Because you have converted the original numbers into text values, you can no longer sum them. You would need a helper column that stores them as numerical values and then reference that helper column.

tgattsh

@Paul Newcome you have no idea just how much I appreciate your help!

So you are saying to do (below)? Could you walk me through storing the numerical values?

helper 1 for "Sales or MTS", helper 2 for Captured Affected FN, and helper 3 for Captured RNI

Paul Newcome

You would use my very first formula where we specify both columns within the CHILDREN functions or the SUM(CHILDREN()) you mentioned.

Then in the checkbox column parent rows you would use

=[helper 1]@row + ""

tgattsh

That worked! I can't thank you enough...

Paul Newcome

Happy to help. 👍️