Contact List column allows multiple entries - using in formula

caw98776 ✭✭
edited 12/09/19 in Formulas and Functions

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!