Contact List column allows multiple entries - using in formula

caw98776

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

 

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.