Contact List column allows multiple entries - using in formula

caw98776 · 08/05/19

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 · 08/05/19

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 · 08/05/19

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 · 08/05/19

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 · 08/07/19

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.

Contact List column allows multiple entries - using in formula

Comments

Help Article Resources

Categories