Return multiple values in 1 cell in Sheet Summary Field
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

Hi @Ashley McAdoo,
To get a count of tasks from a contact multipicklist, 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

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!

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.

Hi @Ashley McAdoo,
To get a count of tasks from a contact multipicklist, 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")))))

Thanks so much! That did the trick!

What is the formular to get the answer susan  8 & tom 7?
Help Article Resources
Categories
Check out the Formula Handbook template!