COUNTIFS and CONTAINS for at least one item in drop down
I have a name drop down and and am trying to count the number of times my name shows in the Leader dropdown (by itself or with others) and keep getting UNPARSEABLE.
=COUNTIFS({CMK - Issue Type}, "Quick Hit", {Harvey Progress}, "<>Full", {Leader}, CONTAINS("Ingrid Larson"; @cell))
I'd appreciate any assistance, please.
Answers
-
Hey @ialarson
The Unparseable is caused by the semi colon in your CONTAINS function - or, depending on your country, the commas instead of semi colons everywhere else. Also, the 'not equal' is not enclosed with quotes, only the textstring you are excluding.
Your formula is either
=COUNTIFS({CMK - Issue Type}, "Quick Hit", {Harvey Progress}, <>"Full", {Leader}, CONTAINS("Ingrid Larson", @cell))
or
=COUNTIFS({CMK - Issue Type}; "Quick Hit"; {Harvey Progress}; <>"Full"; {Leader}; CONTAINS("Ingrid Larson"; @cell))
Will either of these work for you?
Kelly
-
Yes! It needed the comma. Thank you. I couldn't even see it anymore I'd been looking at it so much.
But...now it's returning a zero for it, when I know there are some that qualify. If there are other items "checked" in the dropdown, besides my name, CONTAINS should still count it, right? Or do I need something different to count if it has my name by itself or with anyone else too?
-
Hey
Yes, CONTAINS should work. You can also try HAS. Note the syntax changes between CONTAINS and HAS
=COUNTIFS({CMK - Issue Type}, "Quick Hit", {Harvey Progress}, <>"Full", {Leader}, HAS(@cell,"Ingrid Larson"))
Assuming HAS doesn't work, let's find the culprit in the formula that is causing the return of 0.
First, one by one, completely delete the range in the formula and then, using the Insert Reference, re-insert your ranges. This will make sure that the correct column was originally inserted.
If that didn't fix the zero response, remove one range and criteria at a time. For instance, remove
, {Leader}, CONTAINS("Ingrid Larson", @cell)
Did you get a response other than zero? Continue eliminating range-criteria pairs until you get a non zero response.
Let me know what you get and we'll work through it
Kelly
-
HAS worked! Thanks again!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!