Is it possible to use =COUNTIFS when there are multiple values in one cell?

Jill V

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.

smartsheet Countif question.png

JamesB

@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)

Jill V

Unfortunately, that did not work.

JamesB

@Jill V

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)

Jill V

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.

JamesB

@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.