Counting in a cell with multiple options
Hello,
I am trying to create a formula that will count each time a department has been selected in a sheet. This column has the option to add multiple departments to one row. How do I make a formula that will count each one?
For example in the screenshot below, I am trying to find out how many times each option was selected.
Can someone help me with a formula for this?
Answers
-
You really need to shorten the name of that column name...
=COUNTIF([Internal Department (select all that apply)]:[Internal Department (select all that apply)], HAS(@cell, "CDx SBU"))
You'll need to change the ending of that formula for each different entry you're counting for.
-
@Mike TV thank you for your help! This worked great with a few modifications since I am referencing another sheet. This is how I was able to get it to work: =COUNTIF({Sheet Name Internal Department column}, HAS(@cell, "CDx SBU")).
I am hoping I can ask for your assistance with my next issue that is related? The above formula was counting how many times CDx SBU was selected in the sheet. I now need to take that a step further and reference another column in that sheet that associates if CDx SBU was selected in the internal department column, and if a specific project type was selected, for example Conferences & Webinars, in another column. This is the formula I attempted but it isn't working.
=COUNTIF({Sheet name internal department column}, HAS(@cell, "CDx SBU"), ({Sheet name project column}, "Conferences & Webinars"))
Any advice would be greatly appreciated! Thank you so much!
-
You'll want to switch from COUNTIF to COUNTIFS which will allow for additional criteria and ranges to review.
-
@Mike TV I was so close! Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!