COUNTIFS/CONTAINS FUNCTION

RySols

Hi All,

 I need help with a formula to Count every instance a name appears in a multi-select row of a column based on criteria from another column first. At present this is my first formula:

=COUNTIFS({CORP ADMIN TEAM TASKS Range 1}, "In Progress", {CORP ADMIN TEAM TASKS Range 2}, "Jane Doe")

 However, it is only counting the name once it is the only option in the cell. So if Jane Doe appears alongside John Doe and Jack Doe in the multi-select cell, it will not count the name.

 

I tried this formula below, but it returns UNPARSEABLE:

=COUNTIFS (CONTAINS ("In Progress", [CORP ADMIN TEAM TASKS Range 1]:[CORP ADMIN TEAM TASKS Range 1]), "TRUE", "FALSE"), CONTAINS ("Jane Doe", [CORP ADMIN TEAM TASKS Range 2]:[CORP ADMIN TEAM TASKS Range 2]), "TRUE", "FALSE").

 

Paul Newcome

You are actually closer to being on the right track with your first. Give this a try...

 

=COUNTIFS({CORP ADMIN TEAM TASKS Range 1}, "In Progress", {CORP ADMIN TEAM TASKS Range 2}, CONTAINS("Jane Doe", @cell))

RySols

Thanks Paul. Appreciate the assistance.This works perfectly if the function is being done on the same sheet. See below:

=COUNTIFS(Status1:Status10, "In Progress", [Assigned To]1:[Assigned To]10, CONTAINS("Jane Doe", @cell))

However, when referencing an external sheet, it doesn't return the correct values as in the example you provided.

 

Any suggestions? .... Once again, appreciate the help.

 

Ryan

 

 

Paul Newcome

What do you mean by "it doesn't provide the correct values"?

 

If it is working correctly on the same sheet as the data, then (as long as the ranges are the same as when you put it on the same sheet and cross-sheet references are built correctly) it should provide the same results on a different sheet.

 

Are you able to provide some screenshots showing the differences?

In your example above you referenced rows 1 - 10 when the formula is on the same sheet as the data... Did you select those same rows when setting up your cross sheet reference?

Try creating a temporary filter on the data sheet so that it reflects the same ranges and criteria as the formula you are trying to build. That will help you verify what is being pulled by the formula.

RySols

Hi Paul,

 I believe I may have figured out the issue.The cross references work fine providing that the data in the multi-select column are names that you physically entered in the column properties. But when the column properties is a contact list, the formula will not work. I've reached out to smartsheets support team to see if they can offer any insight into this problem. I'll be sure to post an update once they get back to me. Thanks again.

 

Ryan

Paul Newcome

The Contact type column would be the issue. CONTAINS doesn't care too much for that column type. Try changing the CONTAINS to a FIND.

 

CONTAINS("Jane Doe", @cell)

to 

FIND("Jane Doe", @cell) > 1

.

One thing to note with this change: FIND is case sensitive whereas CONTAINS is not, so you will want to be sure to keep that in mind.

 

If you are using "jane doe" in the FIND function, it will not find Jane Doe. Likewise using "Jane Doe" in the FIND function, it will not find jane Doe, Jane doe, Jane, Doe, or anything else.

 

It can search in a text string of multiple names, but the case sensitivity is something to keep in mind.

RySols

Hi Paul,

Just an update. That solution was almost spot on, however the correct one is below:

=COUNTIFS({CORP ADMIN TEAM TASKS Range 1}, "In Progress", {CORP ADMIN TEAM TASKS Range 2}, FIND("Jane Doe", @cell) > 0)

Instead of using FIND("Jane Doe", @cell) > 1), it should be FIND("Jane Doe", @cell) > 0)

The formula is working perfectly with this solution and capturing the correct values inclusive of being able to work in the Contacts Properties column.

Paul Newcome

@RySols

You are absolutely correct, and that was my mistake. I use the FIND function in this very way quite frequently, so I think maybe my fingers were moving faster than my brain. My apologies. Glad you got it figured out though!!

Joseph Krezo

Try

=COUNTIFS({CORP ADMIN TASKS Range 1}, "In Progress", {CORP ADMIN TASKS Range 2}, HAS(@cell, "Jane Doe")

Paul Newcome

@Joseph Krezo

The problem with the HAS function is that it will only count if that is the only name in the cell. Since there is the possibility of multiple names, we need to be able to check the entire string for a match within it as opposed to an exact match on the string.

Zachary Umetani

I have a similar situation, but would need to count each instance of "Doe". If the cell contained Jon Doe, Jane Doe and Jen Doe I would like the output return 3. I attempted the solution given, but it only returns 1. Thank you.

Paul Newcome

For this you would need to parse out the names into their own cells and count them that way.

kyle.macleod

I'm having the exact same problem, but using CONTAINS isn't working for me. Instead of looking for contains "text" search, I'm trying to look for contains {cell reference contents).

The formula below works fine as long as the searched cell only contains one thing. If there are multiple options, it doesn't count at all.

Whenever I try the syntax above and use the CONTAINS I get an error.

Note, the first part of that formula I put there to eliminate the response of "0", as that messes up with my dashboard graphs. I think I'm just messing up my () somewhere... although also I'm not exactly sure how @cell works.

Any ideas? I'm tired of trying and reaching out for help from you experts. Thank you in advance.

Paul Newcome

@kyle.macleod You should be able to use the syntax of

=COUNTIFS({Range}, CONTAINS([Primary Column]@row, @cell))

kyle.macleod

Paul, you rock. thank you.

For whatever reason, when I copy/paste it didn't work. But when I slowly build each argument one at a time, it works. I probably had an extra (or missing) syntax somewhere.

Paul Newcome

Happy to help! 👍️