How to list values in a cell based on another cell with distinct names (in a list format too)
Hi,
We are trying to present the labor hours workers have dedicated to one same task on different instances.
We started by nesting
=JOIN(DISTINCT(COLLECT({Worker}, {Task}, "DC6" + [Task Label]@row), ", "), ", ")
We have successfully filtered a list of workers that have dedicated hours to a task. We would then want to present a sum of the hours each worker has dedicated to this same task, in the same order their name appears on the previous list, regardless of how many attempts it has taken them to complete such task. Right now, the Worker Hours shows a list of the hours each worker has dedicated to the "Manager" task in order of instance read from our database.
The issues we are encountering are that names don't always appear in the same order, nor we know how to nest several sumifs() to add labor hours for each worker. The desired output for the Worker Hours cell would be {10.5,3.5,6}.
Any help would be appreciated.
Thank you very much.
Best Answers

Hi @Jacobob
First, the structure of SUMIFS() would be like this:
SUMIFS({Hours}, {Task}, Task@row, {Worker}, Worker 1)
Second, I would use the following formula for Worker 1.
Worker 1: INDEX(DISTINCT(COLLECT({Worker}, {Task}, Task@row)), 1)
Then, use the same structure for Workers 2, 3, etc., only changing INDEX()'s row_index.
Worker 2: INDEX(DISTINCT(COLLECT({Worker}, {Task}, Task@row)), 2)
Finally, combine the above formula parts to create an Hours list formula using Line Feed (CHAR(10)) as a delimiter.
=IFERROR(SUMIFS({Hours}, {Task}, Task@row, {Worker}, INDEX(DISTINCT(COLLECT({Worker}, {Task}, Task@row)), 1)), "") + IFERROR(CHAR(10) + SUMIFS({Hours}, {Task}, Task@row, {Worker}, INDEX(DISTINCT(COLLECT({Worker}, {Task}, Task@row)), 2)), "") + IFERROR(CHAR(10) + SUMIFS({Hours}, {Task}, Task@row, {Worker}, INDEX(DISTINCT(COLLECT({Worker}, {Task}, Task@row)), 3)), "") ...
(For output like {10.5,3.5,6}, please substitute CHAR(10) with ",".)
If you use a contact list for the Worker column since the maximum number of the contact list display is fifty, a formula of up to fifty workers would be sufficient, but I wonder if a case using such a long contact list is rare. (I created a formula of up to ten workers in the following demo.)
For details, please look at the demo dashboard at the following link.
https://app.smartsheet.com/b/publish?EQBCT=1d591109affc4859985cccc69738c85f

Happy to help!😀
Answers

Hi @Jacobob
First, the structure of SUMIFS() would be like this:
SUMIFS({Hours}, {Task}, Task@row, {Worker}, Worker 1)
Second, I would use the following formula for Worker 1.
Worker 1: INDEX(DISTINCT(COLLECT({Worker}, {Task}, Task@row)), 1)
Then, use the same structure for Workers 2, 3, etc., only changing INDEX()'s row_index.
Worker 2: INDEX(DISTINCT(COLLECT({Worker}, {Task}, Task@row)), 2)
Finally, combine the above formula parts to create an Hours list formula using Line Feed (CHAR(10)) as a delimiter.
=IFERROR(SUMIFS({Hours}, {Task}, Task@row, {Worker}, INDEX(DISTINCT(COLLECT({Worker}, {Task}, Task@row)), 1)), "") + IFERROR(CHAR(10) + SUMIFS({Hours}, {Task}, Task@row, {Worker}, INDEX(DISTINCT(COLLECT({Worker}, {Task}, Task@row)), 2)), "") + IFERROR(CHAR(10) + SUMIFS({Hours}, {Task}, Task@row, {Worker}, INDEX(DISTINCT(COLLECT({Worker}, {Task}, Task@row)), 3)), "") ...
(For output like {10.5,3.5,6}, please substitute CHAR(10) with ",".)
If you use a contact list for the Worker column since the maximum number of the contact list display is fifty, a formula of up to fifty workers would be sufficient, but I wonder if a case using such a long contact list is rare. (I created a formula of up to ten workers in the following demo.)
For details, please look at the demo dashboard at the following link.
https://app.smartsheet.com/b/publish?EQBCT=1d591109affc4859985cccc69738c85f

Thank you very much for your help, it was without doubt a super useful guidance!

Happy to help!😀
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!