Using a nested HAS formula within a COUNTM?

Tracey Riley

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!

Paul Newcome

The problem is that you are searching for "IT". CONTAINS is not case sensitive, so you are also picking up on the "it" string in "Community".

EDIT: Accidentally hit "Post" before I was done.

To account for case sensitivity, let's switch to the FIND function.

=COUNTIFS({FY21_AOP_MASTER ROLLUP Range 5}, FIND("IT", @cell) > 0)

Just keep in mind that FIND is case sensitive, so if you are searching for "Community", it will not count "community" because the first letter is lower case as opposed to being upper case like your string to search for.

Paul Newcome

You will actually want to use a combination of COUNTIFS and CONTAINS.

=COUNTIFS([Support 1]:[Support 1], CONTAINS("IT", @cell))

Tracey Riley

@Paul Newcome thank you for the response!

So I tried this, but because I am linking to a different sheet for the column reference it looked like this:

=COUNTIFS({FY21_AOP_MASTER ROLLUP Range 5}, CONTAINS("IT", @cell))

While I didn't get an error, it didn't appear to add it correctly. It gave me the number six when I know from counting them that it should only be three... hmm...

Paul Newcome

Are these the ONLY options, or are there others?

IT

Marketing & Communications

Project Management

Research & Evaluation

.

.

Is your range covering multiple columns?

Tracey Riley

@Paul Newcome

See the attached screenshot. This is the comprehensive list of drop-down multi-select options:

Technically, I am going to want to repeat this formula for two other support columns, but Im not there yet since I can't even figure out how to do it for one column.

I tested it in another column and it gave me the exact same numbers...

Tracey

Paul Newcome

The problem is that you are searching for "IT". CONTAINS is not case sensitive, so you are also picking up on the "it" string in "Community".

EDIT: Accidentally hit "Post" before I was done.

To account for case sensitivity, let's switch to the FIND function.

=COUNTIFS({FY21_AOP_MASTER ROLLUP Range 5}, FIND("IT", @cell) > 0)

Just keep in mind that FIND is case sensitive, so if you are searching for "Community", it will not count "community" because the first letter is lower case as opposed to being upper case like your string to search for.

Tracey Riley

@Paul Newcome WOW! You saved the day! THANK YOU!

😍

Paul Newcome

Happy to help! 👍️

Please don't forget to mark the most appropriate response(s) as "helpful" so that others searching for a similar solution can know that one may be found here.