Counting Content of a Multi-select Dropdown

Hello, I have an issue that was more than me and I am seeking help.

I have this column, which contains information about the "Calendar week number" during which an event occurred, as you can see in the first screenshot. An important piece of information here is that the calendar week column is a multi-select dropdown.

P.S: Each row belongs to a different project.


Now, (in another sheet) I need to count how many events happened during a specific calendar week, regardless of the projects to which they belong. In the second screenshot, that is kind of what I want. But I filled this manually. I am interested in using a formula that will automatically count these. For example, how many times in column that column (in screenshot 1 above), was the calendar week number "36" selected? Answer = 2

And I want to include the answers in the column "Total Termine" in screenshot 2.

I tried combining different functions, mainly COUNTIF, COUNTM, HAS, etc., in different orders and combinations, but was unsuccessful. I am missing something and it would be nice to receive some external insight.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!