Using COUNTIFS to count specific words

Midori Vasquez

I have a sheet with a multi-select containing topics such as: Age, Disability, Wage-Hour, Sex, Sexual Harassment, Sexual Orientation, and other unique values. I am using this formula: =COUNTIFS({Drop1}, CONTAINS(Claim@row, @cell), {Month}, 1) to count specific instances by month and it works except for Age and Sex because of Wage-Hour and Sexual Harassment or Sexual Orientation.

I figured out how to count Age without counting Wage-Hour using =COUNTIFS({Drop1}, FIND("Age", @cell) > 0, {Month}, 1). But I am having issues with counting Sex. There are issues where Sex, Sexual Harassment, and Sexual Orientation may be selected for one cell. I have tried =COUNTIFS({Drop1}, "Sex", {Drop1}, NOT(FIND("Sexual", @cell) > 0), {Month}, 3) but it eliminates Sexual Harassment and Sexual Orientation resulting in 0 for Month 3 - the count should be 1. The first two formulas count 2 instances for Sex for Month 3.

Any help would be appreciated.

David Joyeuse

Hi @Midori Vasquez

Your Drop 1 column is a multi dropdown list. The HAS function, instead of FIND, would be better suited if you want to report each instance separately.

https://help.smartsheet.com/function/has

Hope it helped!

Midori Vasquez

Thanks @David Joyeuse. I tried and eliminated the HAS function because it will only return if it is an exact match. So if there are multiple selections for the cell, HAS, in combination with COUNTIFS, will not count the value.