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!
Brady
Best Answer
-
"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.
Answers
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!