Counting in a cell with multiple options

Lindsay Megenhardt

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?

Mike TV

@Lindsay Megenhardt

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.

Lindsay Megenhardt

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

Mike TV

@Lindsay Megenhardt

You'll want to switch from COUNTIF to COUNTIFS which will allow for additional criteria and ranges to review.

Lindsay Megenhardt

@Mike TV I was so close! Thank you!