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.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!