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"?

Find more posts tagged with

Formulas

Comments

Ezra

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.

caw98776

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".

Ezra

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

Paul Newcome

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Issues with Attachments
Ok, I'm not new to Smartsheet but recently this simple automation has had me stumped. Pictured below is a simple automation we use to verify that an attachment has been added to each row in our system. IF attachment added or changed, THEN Change Cell Value. These worked beautifully all over our system until about a month…
Retrieving Cell info if another column falls between specific dates
Here is the formula I am trying and it's brining back #CIRCULAR REFERENCE =IF(AND([FPI Date]@row >= DATE({StartYr}, {StartMth}, {StartDy}), [Jan FPI]@row <= DATE({EndYr}, {EndMth}, {EndDay})), [eCOA_ID]@row , "")
Index/Match
I have two sheets that are capturing two different sets of data. Sheet 1 (Billable Mileage Log) is capturing projects that have been marked as needing mileage to be entered in a different platform. Sheet 2 is capturing mileage that's being entered via a Smartsheet form. On this sheet, I have two columns that have formulas.…