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

✭✭✭✭

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!

• ✭✭✭✭✭✭

"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.

• ✭✭✭✭✭✭

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.

• ✭✭✭✭
edited 08/19/22

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!

• ✭✭✭✭✭✭

"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.

• ✭✭✭✭✭✭

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.

• ✭✭✭✭

@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!

• ✭✭✭✭✭✭

Here is some @row documentation:

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

• ✭✭✭✭

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.

• ✭✭✭✭✭✭

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!