Counting multi select column that is also contact list

I have Contact list - multiple resources can be assigned task

I want to count the number of times a particular resource has been assigned a task.

I've tried these both:

=COUNTIF({Staff Actions Range 4}, HAS("Joe X", {Staff Actions Range 4}))

=COUNTIF({Staff Actions Range 4}, CONTAINS("Joe X", {Staff Actions Range 4}))

Both return 0 which is not correct

Best Answer


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    HAS looks for an exact match, so it will not count any cells that have additional people listed.

    CONTAINS just doesn't like contact type columns.

    I suggest a FIND function instead. The FIND function will search the cell for your specified text and will produce a number based on where within the string your text is found. That means a 0 (zero) implies the text is not found, and any number greater than zero means that the text is found somewhere.

    =COUNTIF({Staff Actions Range 4}, FIND("Joe X", @cell) > 0)

  • Paul apologies for asking an ignorant question but the @cell references what exactly? In my formula I'm referencing a different sheet --

    countif({Staff Actions Range 4}, FIND("Joe X", {Staff Actions Range 4} > 0))

    This returns invalid action....

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The @cell reference basically tells the formula to look across the previously established range and evaluate on a cell by cell basis. There is also a misplaced parenthesis in your formula. You need to close off the FIND function before the greater than zero portion. As long as your cross sheet reference was created using the appropriate steps, you should be able to use the formula I provided exactly as it is.

  • OMG!! Thank you!!😀

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help!👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!