Using a nested HAS formula within a COUNTM?

Hi there! For the life of me I can't figure out this formula! So here is the scenario. I have a grid with a dropdown multi-select column type with different organizational shared service options (e.g. Knowledge Sharing, Project Management, IT, Applied Learning). On a separate grid, I am trying to identify the number of times each of the shared service options (options within the multi-select list) were referenced. I have attached screenshots of what the column looks like. In reference to the screenshot value only, the formula I am writing should come out with these numbers (because this is how many rows reference these services):

IT - 3

Marketing & Communications - 4

Project Management - 2

Research & Evaluation - 1

A couple things to note: 1) Each row of the grid is an annual operating objective submitted by division leads. The purpose of this formula is to help each shared service lead know how many operating objectives require their help. 2) The grid that I am pulling from has linked cells from individual division AOP grids.

I have tried a bunch of different ways to count these by shared service type including a report (which didn't work because I am trying to create a graph on a dashboard with this information), a sheet summary (which also didn't work), and now a separate grid. I keep messing around with some form of:

COUNTM({separate sheet reference sheet}, HAS("shared service name")) - this didn't work. I also tried:

COUNTM(HAS({separate sheet reference sheet}, "shared service name")) - this didn't work either.

Can someone help me out? I have also tried using CONTAINS and COUNTIF and I am about to give up all together!

Any help is appreciated!

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!