Contact List column allows multiple entries - using in formula
I am trying to obtain the number of tasks with a status of "Red" by assigned user. The author column only had one individual assigned and the formula
=COUNTIFS(Author:Author, ="Jane Doe", Status:Status, ="Red")
returned precisely what I needed. I'm trying to replicate the results for the contributors. However, in many instances, there is more than one contributor assigned in that column. How can I get it to count the row including "John Doe" if the cell includes both "John Doe" and "Susie Someone"?
Comments
-
I was trying out some options to achieve this...
- =COUNTIF(Author:Author, CONTAINS("Jane Doe", Author@row))
- doesn't work right... feels like it should, but nope.
- adding a helper column to look at each cell...... =IF(CONTAINS("Jane Doe", Author@row), 1, 0)
- this gives a 1 or 0 depending on if it finds the text string.
- from this you could just count up all of the 1's in that column.
- =COUNTIF(Author:Author, CONTAINS("Jane Doe", Author@row))
-
In an effort to keep things simple, I didn't share that I want to replicate this formula to get counts by contributor for Yellow and Green status as well. So, a helper column won't fix the problem. I wasn't aware of CONTAINS as an option so I modified my formula by replacing equal with CONTAINS but that didn't work - says "unparseable".
-
So... In order to build these "Jane Doe RYGB counts," I see no other solution. Having a helper column to calculate weather or not "Jane Doe" is in your [Author] column allows you to perform an overall count in some other column.
The [count JaneDoe] column would only contain:
- =IF(CONTAINS("Jane Doe", Author@row), 1, 0)
The [calulation info] column (or whatever) would have these formulas:
- =COUNTIFS([count JaneDoe]:[count JaneDoe], >0, [Status]:[Status], ="Blue")
- =COUNTIFS([count JaneDoe]:[count JaneDoe], >0, [Status]:[Status], ="Green")
- =COUNTIFS([count JaneDoe]:[count JaneDoe], >0, [Status]:[Status], ="Yellow")
- =COUNTIFS([count JaneDoe]:[count JaneDoe], >0, [Status]:[Status], ="Red")
I just can't seem to get it to generate a parsable value with the CONTAINS inside a COUNTIFS
-
No helper column needed. Try using a FIND statement in place of CONTAINS.
=COUNTIFS(Author:Author, FIND("Jane Doe", @cell) > 0, Status:Status, "Red")
.
Using CONTAINS on a Contact type column where the option is selected to have multiple contacts per cell does not work.
HERE is a thread dedicated to the CONTAINS function and any quirks people have found with it.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!