Using COUNTIF to determine quantity of rows with number not ending is 000

Randy Darling

 Hello, 

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. 

Mike Wilday

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. 

Randy Darling

Mike, thanks for the reply however i need to count all accept for the line items ending in '000'

Paul Newcome

Give something like this a try...

 

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

Randy Darling

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.

Paul Newcome

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

Mike Wilday

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