Counting Rows with multiple contact columns

I need to know how to count a row if a specific user is in ANY of 3 contact columns in a specific row with the possibility of multiple users - HELP😫

=COUNTIF(OR({ProgMgr}, CONTAINS("Bob", @cell), {ProjMgr}, CONTAINS("bob", @cell), {ChMgr}, CONTAINS("bob", @cell))

Best Answer

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    edited 10/01/20 Answer ✓

    Ah, so a unique count in the column. I feel like Distinct might help here, but I couldn't get it to work. You could do it by adding an IF and returning a 1 or 0 for the SUM. Like this:

    =SUM(IF(COUNTIF([ProgMgr]:[ProgMgr], CONTAINS("Bob", @cell)) > 0, 1, 0), IF(COUNTIF([ProjMgr]:[ProjMgr], CONTAINS("Bob", @cell)) > 0, 1, 0), IF(COUNTIF([ChMgr]:[ChMgr], CONTAINS("Bob", @cell)) > 0, 1, 0))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!