COUNIFS formula when multiple contacts

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!

Find more posts tagged with

Construction

Formulas

Project Management

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?