Need help - adding less than or equal value to a current formula


Currently I'm struggling to add <= value to my current formula code, my idea is to count for Order Numbers (Unique values) and Lines of Order Numbers of certain times.

since filter doesn't work in this code, I'd like to add a <= or >= value formula for Ship_Time, so my code filters can automatically generate statistics for certain times I need.

For Orders I am using this formula:

=COUNT(DISTINCT(COLLECT({New Sheet Range 2}, {New Sheet Range 1}, ="SZE")))

here I want to add for example ">=09:00:00"

For Lines formula:

=COUNTIF({New Sheet Range 1}, [Delivered By]@row)

looking for suggestions and help!

Thank you.


Best Answer


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Time is currently stored as a text value in Smartsheet and cannot be compared as less than or greater than. You will need to use a helper column to convert it into a numerical value and then reference that column.

    There should be a solution somewhere in this thread. It is currently up to 13 pages long, but I am fairly certain there should be something in there to help get you started.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭


    To add to Paul's answer - once you have your numerical values set for time, you can simply add the remote column range for your time column and the ">=" criteria to the COLLECT function. COLLECT allows you to specify multiple pairs of criteria range and criteria.

    Syntax: COLLECT( range, criterion_range1, criterion1, criterion_range2, ​criterion2)

    =COUNT(DISTINCT(COLLECT({New Sheet Range 2}, {New Sheet Range 1}, ="SZE", {New Sheet Numerical Time Range}, >= 9)))


    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!