How to countifs "<1"


Hi Smartsheet Community!

I have a calculation sheet and I am using COUNTIFS to pull the count of each option in a range of <1,1,2,3,4,5. These criteria were not chosen by me and must remain as is.

I am easily able to do this for 1-5 but Is it possible to use COUNTIFS to count how many "<1" there are? Is there a better way?

Thank you,


Best Answers

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

    @Nick Korna Did you test your second one? I feel like if there is a blank cell the VALUE function would throw an error (haven't tested myself though). If it does, we would need to incorporate an IFERROR like so:

    =COUNTIFS([Column name]:[Column name], IFERROR(VALUE(@cell), 2) < 1)

    The IFERROR will output a 2 if the VALUE function throws an error. Since 2 is not less than 1, it won't get included in the count. I haven't tested this for blanks, but I have run into this issue when there was the potential for text values such as letters.

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

    Ok. This may be it. You have two different data types in the same column, (text and numerical), and most functions don't play well with that.

    Insert another column on the source sheet (it can be hidden after setup) and use this:

    =[Column Name]@row + ""

    (Plus double quotes)

    Then try your COUNTIFS referencing this new helper column.

    =COUNTIFS({Position Title}, @cell = $[Position Titles]@row, {Helper Column}, @cell ="<1")

    Note: You may also need to put quotes around the numbers in the other COUNTIFS once you adjust them to also look at the helper column.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!