I'm trying to setup a "Line Quantity" of children where the primary column contains a 5 digit number. These numbers indicate line items of a purchase order and have a hierarchy themselves. The numbers that are considered the parent end in 000 with all the items that are children end in various numbers in increments of 10. With that, there are additional line item numbers that are simple two digit items that don't have a parent. The reason i need to negate the parent is due to the fact that the parent never ships and always includes a quantity. Otherwise i can do a simple count of the range. I would indent the children under the parent however this info will be overwritten every 3-5 days when the tracking is updated by the manufacturer so all my formulas and hierarchy need to be outside of the imported ranges. I've tried various formulas which include COUNT, COUNTIF, SUMPRODUCT treating the line item number as text. I can get it to work in excel and access but not smartsheet. 


This is an easy one! In each Package row use the following formula: =Count(Children()) 

This will count the number of child rows in each package row. The only caveat is if the child rows also have indented rows under them. The formula will only count the direct children of the row that the formula is in. 

Give something like this a try...


=COUNTIFS([Package/Line Item]:[Package/Line Item], RIGHT(@cell, 3) <> "000")

I solved my own problem wherein i noticed there was an item with a line number indicating a parent with no children. Therefor it need to be counted. i've decided that in order for this to work and be saved as a template for use i would have to simplify and just count the parent items ending in 000 that had no children and manually enter. The circled parent line item was the clincher. if anyone else has a more elegant way to solve this i'm all eyes.

In reply to by Randy Darling

=COUNTIFS([Package/Line Item]:[Package/Line Item], OR(COUNT(CHILDREN(@cell)) = 0, RIGHT(@cell, 3) <> "000"))


See if something like the above will work for you...

Let us know if Paul's solution will work. Sorry, I didn't fully notice your requirement. frown