COUNTIF using Parent Rows Only

Jill Youngberg

I am trying to come up with a formula that would count only the parents rows.

J. Craig Williams

Jill,

 

A bit clarification please.

All parents in a column?

What about grand-parents, do they count?

 

You may need to have a column determining if the row is a parent and then count that column instead.

 

I was hoping something like this would work:

 

=COUNTIF(Example:Example, COUNT(CHILDREN(@cell)) > 0)

 

but the second level of @cell usage seems to confuse it (#UNPARSEABLE).

 

Craig

Brett Evans

Jill,

I have been able to do that, but it took about 8 extra columns.  It can handle grandparent, parent and child rows.  My head started to hurt when adding another level.  

 

To Craig's question, what is the full use case that you are trying to achieve?  The details can change the layout of this.

 

-Brett

Jill Youngberg

I am pretty new at this so bear with me a bit.

I have a parent row that rolls up information based on dates in the children rows. I am wanting to have a count of the parent rows only for each set (based on the different information that is rolled up per set)

Shaine Greenwood

Jill—

 

One possibility would be to add a parent row at the top of your sheet and place a =COUNTIF(CHILDREN(), DATE(yyyy,mm,dd))

 

You'd need to do a lot of indenting and doublechecking to make sure that your hierarchy is still in order.

 

Otherwise, you can place the formula in another cell (where ever you'd like), and reference the parent cells: 

 

=COUNTIF(CHILDREN([Column Name]4), DATE(yyyy,mm,dd)) + COUNTIF(CHILDREN([Column Name]20), DATE(yyyy,mm,dd)) + etc...

 

I'd recommend checking out our Creating Formulas article for details on function syntax in Smartsheet: https://help.smartsheet.com/articles/2476171-create-and-edit-formulas-in-smartsheet

 

You can find our complete list of functions available in Smartsheet here: https://help.smartsheet.com/articles/775363-using-formulas