Is it possible to use =COUNTIFS when there are multiple values in one cell?
I track monthly metrics for my company. I am trying to count the number of times a specific state was completed during each month. The Smartsheet I am I need to collect this data has a multi select dropdown.
I typically use =COUNTIFS({Smartsheet Range1}, "CA",{Smartsheet Range2},>=Date1,{Smartsheet Range2},<=Date2) which works if there is only 1 value in the cells. However if there are multiple values in a cell they do not get counted.
Changing the formula to =COUNTIFS({Smartsheet Range1}, CONTAINS("CA",{Smartsheet Range1}), {Smartsheet Range2}, >=Date1, {Smartsheet Range2}, <=Date2) , using "*CA*" or "CA*" were unsuccessful also.
Answers
-
@Jill V Try using the HAS function instead of the contains function. it is similar but reverses the lookup.
=COUNTIFS({Smartsheet Range1},HAS({Smartsheet Range1},"CA"), {Smartsheet Range2}, >=Date1, {Smartsheet Range2}, <=Date2)
-
Unfortunately, that did not work.
-
I just noticed one more thing about your formula. I added @row to your Date1 and 2 arguments provided that Date1 and Date2 are your column names.
=COUNTIFS({Smartsheet Range1},HAS({Smartsheet Range1},"CA"), {Smartsheet Range2}, >=Date1@row, {Smartsheet Range2}, <=Date2@row)
-
Hello @JamesB - Thank you for your advice, unfortunately, I believe due to our team having multiple sheets and a separate sheet strictly for metrics I cannot utilize the @row option.
-
@Jill V I am not sure I understand. The @row was added because your formula needs to know where the reference date is located at to perform the action in the formula.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!