How to list values in a cell based on another cell with distinct names (in a list format too)

Jacobob · 04/07/23

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.

jmyzk_cloudsmart_jp · 04/09/23

Hi @Jacobob

list values in a cell based on another cell.png

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

jmyzk_cloudsmart_jp · 04/11/23

Happy to help!😀

jmyzk_cloudsmart_jp · 04/09/23