How do I determine the number of top-level rows in sheet without a helper column?

bsikes

Hello,

As part of a larger formula, I'm trying to determine the total number of top level rows (rows without parent rows) in a sheet. My goal, is to do this without the use of a helper column. None of the formula's I've tried have worked, and I'm starting to think its due to a misunderstanding on my part or a lack of documentation on where the @row/@cell references can be used. 

Here's my mock sheet that illustrates the scenario

Level: =COUNT(PARENT())

NumberOfParentsWithHelper: =COUNTIF([Level]:[Level], 0)

NumberOfParentsNoHelper@row: =COUNTIF([Entry]:[Entry], COUNT(PARENT([Entry]@row)) = 0)

NumberOfParentsNoHelper@cell: =COUNTIF([Entry]:[Entry], COUNT(PARENT(@cell)) = 0)

My (probably incorrect) understanding was that in the context of a COUNTIF function, the @row/@cell references would be using the current row/cell being evaluated in the COUNTIF range. However, the formula referencing @row returns an incorrect result, 0, while the @cell formula is unparseable.

Would someone be able to help me understand why neither of my two helperless formulas are working? 

Thank you!

Brady

Paul Newcome

"Entry@row" is going to evaluate what level the row is that the formula is in. If it is there in row 1, then there is no Parent row to evaluate.

"@cell" cannot be used at all with hierarchy based functions (ANCESTORS, PARENT, CHILDREN, DESCENDANTS) and will throw an error every time.

Paul Newcome

You would need either a helper column or some kind of indicator on every row within an existing column to denote which rows are top level and which are not.

bsikes

Thanks for the response @Paul Newcome!

Would you be able to explain why neither of my two formulas are working though? I'm wanting to fix my misunderstanding so I don't run into the same problem again.

My assumption was that as the COUNTIF is going through each cell in the range [Entry]:[Entry], that it would be properly evaluating the condition COUNT(PARENT([Entry]@row)) = 0) for each cell. Obviously it's not working, but I can't figure out what it is actually doing and why it's not working as expected.

Thank you!

Paul Newcome

"Entry@row" is going to evaluate what level the row is that the formula is in. If it is there in row 1, then there is no Parent row to evaluate.

"@cell" cannot be used at all with hierarchy based functions (ANCESTORS, PARENT, CHILDREN, DESCENDANTS) and will throw an error every time.

Jason Duryea

In our project schedules, we use two helper columns to determine this.

[Level] which has a formula of COUNT(ANCESTORS())

[Children] which has a formula of COUNT(CHILDREN())

These are both set as column formulas, not cell formulas.

If both of these are equal to 0 then you know it's a stand-alone row. These could probably be combined into a single formula to simplify, but it would still require at least one helper column. We primarily use this for use in the Conditional Formatting.

bsikes

@Paul Newcome got it. I was starting to assume both of those cases, but I couldn't find any documentation to confirm that... Do you know if there's any documentation on that? The hierarchy function help pages don't list that "@cell" limitation, and I couldn't find anywhere that explicitly said "@row" only refers to the formulas row. There's lots of references to using them in formulas, but I couldn't find much on how they themselves actually work.

I just want to make sure there's not some other source of documentation that I'm not aware of.

@Jason Duryea : Yeah, I was hoping to avoid adding helper columns if possible. There's other criteria I have that will need to be evaluated in a similar method manner, which will require more helper columns if I'm unable to bundle everything together in a single formula. Seems like the use of helper columns is unavoidable though!

Paul Newcome

Here is some @row documentation:

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell#toc-use-row-to-improve-formula-performance

And here is an article that explains not being able to use the hierarchy based functions in cross sheet references.

https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets

bsikes

Thanks @Paul Newcome! I didn't realize the hierarchy formulas didn't support cross sheet references... that throws what I was hoping to accomplish out the window in the first place. Looks like many helper columns are in my future.

Paul Newcome

Happy to help. 👍️