Formula to count if child row is checked 'active'; if no child rows, then look at current row


I need a formula that

  • Determines if the row has child rows
  • If yes, then count the number of child rows that have the 'Active' checkbox checked and show that total in the parent row Active Sites column.
  • If no child rows, then look at the Active checkbox in that current row and show a 1 in the Active Sites column if it is checked, 0 if not checked.
  • If the row is a child row, I'd still like the Active Site column to show a 1 if the box is checked.

Here is my formula so far:

=IF(COUNT(CHILDREN() >= 1), COUNTIF(CHILDREN(Active@row), 1), IF(Active@row = 1, 1, 0))

It works for the Parent Row, but doesn't do anything in the rows that are either a child, or a row that has no child rows.

Any suggestions?

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!