trying to use countifs with multiple criteria's and a multiple selection drop down list

tony.cimino

i've tried countif, countifs, count/collect. has, contains, i either don't get the correct value, or different errors such as incorrect argument, unparsable, ect.

The formula that is shown comes back with a value of 0, when on the reference sheet it should be 1 for this column, during this month, for this employee.

The column name as shown are the items in the drop down list, however there may be times when there are multiply items per cell that I am trying to count.

I'm trying to build a metric by column name, month, and employee from a different sheet.

Kelly Moore

Hey @tony.cimino

If I understand correctly, the Name column on the source sheet can be a multiselect dropdown, that is, you are able to select multiple values within the same cell. If my understanding is correct then the functions HAS, CONTAINS or FIND are functions that work with multiselect columns.

Try this:

Within your existing formula replace {OCC QA Data Entry Version 2.0 Employee}, $Month$36,

with

{OCC QA Data Entry Version 2.0 Employee}, CONTAINS($Month$36, @cell),

Also, without seeing your source sheet, I will assume that $Month$36 is a correct value for the {OCC QA Data Entry Version 2.0 Employee} range.

Does the replacement give you the result you are expecting?

Kelly

Kelly Moore

Hey @tony.cimino

If I understand correctly, the Name column on the source sheet can be a multiselect dropdown, that is, you are able to select multiple values within the same cell. If my understanding is correct then the functions HAS, CONTAINS or FIND are functions that work with multiselect columns.

Try this:

Within your existing formula replace {OCC QA Data Entry Version 2.0 Employee}, $Month$36,

with

{OCC QA Data Entry Version 2.0 Employee}, CONTAINS($Month$36, @cell),

Also, without seeing your source sheet, I will assume that $Month$36 is a correct value for the {OCC QA Data Entry Version 2.0 Employee} range.

Does the replacement give you the result you are expecting?

Kelly

tony.cimino

Yes, that worked after I had changed some of my formula around to this.

Paul Newcome

Quick tip:

Whenever I see something that could potentially be contact type data

{OCC QA Data Entry Version 2.0 Employee}

I lean towards HAS or FIND functions. The CONTAINS function doesn't work on contact type columns and can be a pain to troubleshoot if that is the only issue.

tony.cimino

the way that I have the sheet set up is that each employee has their own section, the only issue i was having was with the [Databar....] since that was the column on the source sheet that was the multi select.

Paul Newcome

@tony.cimino Yes. I saw that when reading through the thread. I was just throwing that tip out there for general reference as I saw @Kelly Moore had used the example of the CONTAINS function with the Employee range. I have seen quite a few threads here in the Community exploring the HAS, CONTAINS, and FIND functions and wanted to throw that little tid-bit out there just in case you or anyone else needed it in the future.