Using COUNTIFS to count specific words

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.

Tags:

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!