COUNTIFS- CONTAINS/FIND/HAS

ka36

Set-up: I have a data prep sheet that references a master sheet and some other sheets so it is easier to set up a dashboard. I have a list of departments in one column (Column5), and the count formula in the next column. The formula works when there is one department listed in the reference but not if there are multiple departments (it is a drop down list and at most there are two in a cell).

Current Formula:

=COUNTIFS({ActivityMasterRange1}, "Active/Published", {ActivityMasterRange2}, [Column5]@row)

Every way I've added in a contains, has, find function returns it as invalid data type or simply zero. I want the formula to check and make sure the activity is active, then check the department and add a count if the department is present (not the only thing in the cell). Any tips appreciated!

sharkasits

@ka36 I used this in a test sheet, it seems like it's working, you need to use @cell, not the range reference again.

Ipshita

@ka36 Great! if the CONTAINS has worked, may be FIND ("Owner", @cell) > 0 should also work.

Do try and let me know. I am just too lazy today to test out the possibilities ;) :)

Cheers!

Ipshita

sharkasits

@ka36 I know you said you tried contains already... but I know I mess up the syntax of those sometimes inside other functions... did you try it like this?

=COUNTIFS({ActivityMasterRange1}, "Active/Published", {ActivityMasterRange2}, CONTAINS(@cell, [Column5]@row))

Ipshita

Hi @ka36

The COUNTIFS formula won't work if your column has more than one value in the same column, i.e. if it's a multi-value enabled dropdown column.

Cheers!

Ipshita

ka36

@Ipshita That seems to be my issue. Is there a workaround on this without having to add a new column for the second department on the master sheet? I simply want it to count the instances that x occurs in a column where x and y might be in the same cell.

@sharkasits I have it where @cell and Column5@row are switched and that gives me zero when it should be one. The code for this is below:

=COUNTIFS({AY 2022-23_ACTIVITY MASTER Range 1}, "Active/Published", {AY 2022-23_ACTIVITY MASTER Range 2}, CONTAINS(Department@row, {AY 2022-23_ACTIVITY MASTER Range 2}))

If I switch to:

CONTAINS({AY 2022-23_ACTIVITY MASTER Range 2}, Department@row))

Then my error return is "INVALID OPERATION"

Ipshita

@ka36 Unfortunately, I haven't seen a workaround. Even we have a column of Owner where we had more than one person assigned to the same task and we had to separate it to just have one person in that column as our COUNTIFS formula wasn't working. May be there is a longer workaround for it where you insert a helper column and have another working sheet on the side, but that is too painful to execute, for what you are trying to achieve :)

Cheers,

Ipshita

sharkasits

@ka36 I used this in a test sheet, it seems like it's working, you need to use @cell, not the range reference again.

ka36

@sharkasits You've solved my day-long problem. I didn't realize the purpose of @cell... Thank you so much!

@Ipshita This worked for me! My new formula is-

=COUNTIFS({AY 2022-23_ACTIVITY MASTER Range 1}, "Active/Published", {AY 2022-23_ACTIVITY MASTER Range 2}, CONTAINS(Department@row, @cell))

I didn't realize that @cell would be still linked to that first criterion reference right before the CONTAINS.

Ipshita

@ka36 Great! if the CONTAINS has worked, may be FIND ("Owner", @cell) > 0 should also work.

Do try and let me know. I am just too lazy today to test out the possibilities ;) :)

Cheers!

Ipshita

ka36

@Ipshita Switching from CONTAINS to FIND and using "> 0" like you listed gave me the same output!

Ipshita

@ka36 - Awesome! Don't forget to hit an "Awesome" on my response if that helped you ;)

Cheers!

Ipshita