COUNIFS formula when multiple contacts

@HELP@

I have a sheet with columns such as "Status of Request"(Dropdown list: New, In Design, Completed) and "Assigned Designer"(My assigned designer column allows multiple contacts). I want to count how many projects under the status "In Design" for Designer "Luis" do I have so I have the formula 

=COUNTIFS([Assigned Designer]:[Assigned Designer], "Luis", [Status of Request]:[Status of Request], "In Design")

and it works but it only counts the cell that has "Luis" as the ONLY designer, so if I have one of the projects with multiple designers it won't count it. What can I do about it?  

I have tried adding the criteria  = (equal to), <> (not equal to), > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to) but they are not accurate. I guess I should be looking for something like "has any of" 

Thanks!

Andrée Starå

Hi,

Try using the new CONTAINS function.

https://help.smartsheet.com/function/contains

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

@HELP@

I can't figure out how to make it work. 

=COUNTIFS(CONTAINS("Luis") , [Assigned Designer]:[Assigned Designer] , [Status of Request]:[Status of Request], "In Design")

=COUNTIFS(CONTAINS("Luis", [Assigned Designer]:[Assigned Designer]), [Status of Request]:[Status of Request], "In Design")

When I put any of these formulas it tells me INCORRECT ARGUMENT SET

 

Andrée Starå

Try something like this.

=COUNTIFS([Assigned Designer]:[Assigned Designer]; CONTAINS("Luis"; @cell); [Status of Request]:[Status of Request]; CONTAINS("In Design"; @cell))

The same version but with the below changes for your and others convenience.

=COUNTIFS([Assigned Designer]:[Assigned Designer], CONTAINS("Luis", @cell), [Status of Request]:[Status of Request], CONTAINS("In Design", @cell))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

@HELP@

No, it didn't. It does take the formula without showing any error but it gives me a 0 value which is not accurate.

 

Andrée Starå

That's strange!

Can you maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Andrée Starå

At a glance, it looks like your formula should work

Try something like this.

=COUNTIFS([Assigned Designer]:[Assigned Designer]; CONTAINS("Daniela P."; @cell); [Status of Request]:[Status of Request]; CONTAINS("In Design"; @cell))

The same version but with the below changes for your and others convenience.

=COUNTIFS([Assigned Designer]:[Assigned Designer], CONTAINS("Daniela P.", @cell), [Status of Request]:[Status of Request], CONTAINS("In Design", @cell))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Dit it work now?

Paul Newcome

One thing I know WILL work is the FIND function for this one...

 

=COUNTIFS([Assigned Designer]:[Assigned Designer], FIND("Luis", @cell) > 0, [Status of Request]:[Status of Request], FIND("In Design", @cell) > 0)

@HELP@

YES! It did work with this formula.

Thank you so much!

Paul Newcome

Excellent. Happy to help! 

 

I am going to do some testing with the CONTAINS function now that SS is back up.

Andrée Starå

Did you test my CONTAINS formula? It worked here when I checked.

Did it work for you? I'm getting used to try that instead of FIND.

Paul Newcome

I started to, got distracted, then forgot to finish. Haha.

 

I did start a "collective knowledge" discussion on the CONTAINS function HERE. I haven't posted any actual information YET, but I intend to at some point today. Feel free to chime in.

Andrée Starå

Haha!

I was asking the poster but you're welcome to test as well.

What are you struggling with?

 

Paul Newcome

Is it a Contact type column? If so, the CONTAINS function will return a 0 if you have the option turned on to allow multiple contacts per cell even if there is only 1 contact in each.

Paul Newcome

Haha. Well I tested as well. See my last post for the results. SPOILER ALERT: CONTAINS doesn't work on a contact type column where the option to have multiple contacts per cell is turned on.

 

I personally am only struggling with remembering it. I have found a few oddities, but overall I have a pretty good understanding of it myself. I just know that with some of the oddities and it being new, it might not be a bad idea to pool all of the knowledge we can into one place to make it easier for people looking for help.

Andrée Starå

Paul,

Yes, that's correct.

I didn't notice it because I didn't use any email address in my test. Without an email address added it works. Strange!