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.
Answers
-
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.
Hope it helped!
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!