COUNTIFS Help - what is wrong with my formula
Can anyone tell me what is wrong with this?
I am trying to count all the cells in column "Monday" that does not contain the word hold (case doesn't matter but I haven't gotten that far) where the cell in the same row in the column Description contains LIRR.
I get the error "Incorrect Argument Set"
=COUNTIFS([Monday]3:[Monday]86, NOT(CONTAINS(("Hold")), Description3:Description86, CONTAINS("LIRR")))
Best Answers
-
Try something like this...
=COUNTIFS(Monday3:Monday86, NOT(CONTAINS("hold", @cell)), Description3:Description86, CONTAINS("lirr", @cell))
Using CONTAINS in a COUNTIFS function like this, you want to use the @cell reference in the criteria portion after establishing your range. This allows you to maintain the proper syntax for the formula and tells the formula to look at each cell individually across the range.
Specifying the range within the CONTAINS function, you are basically telling the formula that if the ENTIRE range contains that text.
-
Try removing a closing parenthesis from the end of the formula.
-
Ugh, should have found that myself. Thank you.
Answers
-
Swap your value and range around. Sample Usage:
IF(CONTAINS("Jacket", [Clothing Item]:[Clothing Item]), "True", "False")
Try this:
=COUNTIFS(NOT(CONTAINS("HOLD", [Monday]3:[Monday]86)), CONTAINS("LIRR", Description3:Description86))
Regards,
Jeff Reisman
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!
-
Ok, I am no longer getting an error but I am not getting the correct number. I am getting the value of "1". But in my sample set I should be getting 10.
I want to count every cell in a range that has a value that does not contain the word hold where the immediate cell in the neighboring column DOES contain the letters LIRR.
-
UPDATE:
It's somewhat working... but due to the conditioning, it is also counting blanks. Here is my current statement:
=COUNTIFS([01/06]3:[01/06]86, NOT(CONTAINS("Hold", @cell)), Description3:Description86, CONTAINS("LIRR", @cell))
If I want to exclude blanks from the count of cells in the column named "01/06", how would I add that condition?
-
Try something like this...
=COUNTIFS(Monday3:Monday86, NOT(CONTAINS("hold", @cell)), Description3:Description86, CONTAINS("lirr", @cell))
Using CONTAINS in a COUNTIFS function like this, you want to use the @cell reference in the criteria portion after establishing your range. This allows you to maintain the proper syntax for the formula and tells the formula to look at each cell individually across the range.
Specifying the range within the CONTAINS function, you are basically telling the formula that if the ENTIRE range contains that text.
-
This works! Thank you!! The only issue is it also now includes blanks when counting how many are not held. How can I add a condition to count all the values that don't contain hold but are also NOT blank?
-
My current attempt at this which is returning Unparseable:
=COUNTIFS([01/13]$3:[01/13]$86, AND(NOT(ISBLANK(@cell)),NOT(CONTAINS("Hold", @cell))), $Description$3:$Description$86, CONTAINS("LIRR", @cell)))
-
Try removing a closing parenthesis from the end of the formula.
-
Ugh, should have found that myself. Thank you.
-
Happy to help! 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!