Using COUNTIF to determine quantity of rows with number not ending is 000
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 35 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.
Comments

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.

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

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.

Let us know if Paul's solution will work. Sorry, I didn't fully notice your requirement.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.2K Get Help
 360 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!