Return multiple values in 1 cell in Sheet Summary Field

Ashley McAdoo

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

EvermoreCoffee

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")))))

EvermoreCoffee

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

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

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

@EvermoreCoffee

Thanks so much! That did the trick!

Abi A

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