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,
Maggie
Best Answers
-
@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.
-
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.
Answers
-
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)))
-
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
-
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?
-
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.
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!
-
@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 - 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):
-
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)
-
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.
-
Hi Christian, yes I did mention that I tried "<1", thanks though!
-
@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)?
-
-
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.
-
@Paul Newcome IT WORKED! Thank you so much!!
-
-
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!
Help Article Resources
Categories
Check out the Formula Handbook template!
