Return multiple values in 1 cell in Sheet Summary Field

Options

Hello everyone,

I am trying to create a sheet summary function. In my column there are 2 people who can be assigned; Tom Jones and Susan Smith. I can get the formulas to work for how many are assigned ONLY to Tom or ONLY to Susan, but I can't get it to work when things are assigned to Tom AND Susan. Any suggestions? I've tried variations on COUNTIFS with AND, OR, CONTAINS, etc. but I can't get it.


Thanks,

Ashley

Best Answer

  • EvermoreCoffee
    edited 08/30/22 Answer ✓
    Options

    Hi @Ashley McAdoo,


    To get a count of tasks from a contact multi-picklist, where a given task has been assigned to multiple people, you can tweak the above formula to something like this:

    =COUNT((COLLECT(Task:Task, Name:Name, AND(HAS(@cell, "Susan Smith"), HAS(@cell, "Tom Jones")))))

    This should go through your contact column and collect any cells where the contact options contain both "Susan Smith" and "Tom Jones"


    In the above image, I tossed the formula to the right of "Both Susan and Tom" returning a count of 2 (basically counting Task 2 and 3).

    Let me know if this helps!

    EDIT: Updated formula to use only a single column:

    =COUNT((COLLECT(Name:Name, Name:Name, AND(HAS(@cell, "Susan Smith"), HAS(@cell, "Tom Jones")))))

Answers

  • EvermoreCoffee
    Options

    Hi Ashley, here is a formula that joins a collection of tasks, based on if a given cell in the collection has either "Susan" or "Tom" in it, joining each task with a comma.

    =JOIN(COLLECT(TaskColumn:TaskColumn, NameColumn:NameColumn, OR(HAS(@cell, "Tom"), HAS(@cell, "Susan"))), ", ")

    Hope this helps!

  • Ashley McAdoo
    Ashley McAdoo ✭✭✭✭✭
    Options

    Hey @EvermoreCoffee ,


    I see what you are trying to do, but I'm not trying to join values from 2 columns. The NameColumn is the only column the formula should search in, and it is a contact list column that is populated through automation. One cell has both Tom Jones and Susan Smith in it, but I cannot get the Sheet Summary to count it.

  • EvermoreCoffee
    edited 08/30/22 Answer ✓
    Options

    Hi @Ashley McAdoo,


    To get a count of tasks from a contact multi-picklist, where a given task has been assigned to multiple people, you can tweak the above formula to something like this:

    =COUNT((COLLECT(Task:Task, Name:Name, AND(HAS(@cell, "Susan Smith"), HAS(@cell, "Tom Jones")))))

    This should go through your contact column and collect any cells where the contact options contain both "Susan Smith" and "Tom Jones"


    In the above image, I tossed the formula to the right of "Both Susan and Tom" returning a count of 2 (basically counting Task 2 and 3).

    Let me know if this helps!

    EDIT: Updated formula to use only a single column:

    =COUNT((COLLECT(Name:Name, Name:Name, AND(HAS(@cell, "Susan Smith"), HAS(@cell, "Tom Jones")))))

  • Ashley McAdoo
    Ashley McAdoo ✭✭✭✭✭
    Options

    @EvermoreCoffee

    Thanks so much! That did the trick!

  • Abi A
    Abi A ✭✭
    Options

    What is the formular to get the answer susan - 8 & tom -7?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!