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
 10.7K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!