How to countifs "<1"

Maggie MacEachern

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,

Maggie

Paul Newcome

@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

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.

Christian G.

Sure it is possible. if the column is numbers, simply use "=Countif([column name]:[column name],<1)

If the cells is a string though, convert it to number and filterout the blank cells with this formula:

=Countifs([column name]:[column name],value(@cell)<1,[column name]:[column name],not(isblanck(@cell)))

Maggie MacEachern

Hi Christian,

Thank you. I'm including a screenshot of my calculation sheet for reference/more clarity:

In this example the columns <1,1,2,3,4,5 reference average days.

There are 5 positions in another sheet that I am pulling the information for, 1 of them has an average day count of 4, and 1 has an average day count of 5. The final 3 are <1 but it will not count them. I use the following formula in my sheet: =COUNTIFS({Position Title}, $[Position Titles]@row, {Average Days}, <1) (FYI I have tried <1 and "<1" with no luck).

I tried your second formula above but I am confused about the Value part. What goes in the (@cell)?

Thank you!

Maggie

Paul Newcome

Exactly how is your source data being populated, and what are the exact formulas you are using to get the counts for 4 and 5?

Nick Korna

Nothing needs to go in the @cell - this is a reference to the cell contents, in this case checking the value is less than 1.

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

There is one slight typo in the formula though, it should be:

=COUNTIFS([Column name]:[Column name], VALUE(@cell) < 1, [Column name]:[Column name], NOT(ISBLANK(@cell)))

Or you can use an alternative for finding not blank values:

=COUNTIFS([Column name]:[Column name], VALUE(@cell) < 1, [Column name]:[Column name], VALUE(@cell) <> "")

Hope this helps!

Paul Newcome

@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.

Nick Korna

@Paul Newcome - I did and it seems to work fine. Adding your formula as another option doesn't seem to work though, as it counts the blank rows as valid (there are 50 rows total, the other 46 are completely blank):

Maggie MacEachern

Hi Paul,

The data is being populated in my source sheet via a smarsheet update request. There is a drop down of the options I mentioned above; They can choose <1,1,2,3,4,5. There seems to be some confusion that there are other variations of "<1". They are not inputting 0.2, 0.3, etc. They are simply choosing <1. I unfortunately cannot change this as it has been requested by the project lead so I am trying to find a way to count "<1" as is.

The exact formulas for column 4 and 5 are as follows:

=COUNTIFS({Position Title}, $[Position Titles]@row, {Average Days}, 4)

=COUNTIFS({Position Title}, $[Position Titles]@row, {Average Days}, 5)

Christian G.

Have you tried this:

=COUNTIFS({Position Title}, $[Position Titles]@row, {Average Days}, ="<1")

It's is a usefull information to know that "<1" is a string and not a number less then 1.

Maggie MacEachern

Hi Christian, yes I did mention that I tried "<1", thanks though!

Paul Newcome

@Nick Korna Ok. Thanks for confirming. I am at ENGAGE this week and haven't had much opportunity to do testing.

In that case I would use this:

=COUNTIFS([Column name]:[Column name], @cell = "<1")

@Maggie MacEachern Are you able to show the dropdown menu that your users are selecting from (screenshot)?

Maggie MacEachern

@Paul Newcome here you go:

Paul Newcome

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.

Maggie MacEachern

@Paul Newcome IT WORKED! Thank you so much!!

Paul Newcome

Happy to help. 👍️

Christian G.

"Maggie MacEachern ✭

09/19/23

Hi Christian, yes I did mention that I tried "<1", thanks though!1 "

It was the "=" sign that was maybe different. Anyway, it works now.

Congrats!