Counting in a cell with multiple options


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
    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.

  • @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
    Mike TV ✭✭✭✭✭✭

    @Lindsay Megenhardt

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!