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

Randy Darling
Randy Darling ✭✭
edited 12/09/19 in Formulas and Functions


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. 



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!