Counting number of tasks per Assigned To contact

Sabdelkadir

Hello- I have a sheet that includes a column of assigned personnel per each task of each project. Some tasks have multiple personnel, and I am hoping to be able to get a format where I can visibly see each individual team member and the total number of tasks they have. I went through past discussions, but could not find a solution to what I need. I am hoping to use a formula that will tell me how many tasks each team member has in totality. My goal is to either create a report from this data to display on our dashboard or a graph for our dashboard.

jmyzk_cloudsmart_jp

Hi @Sabdelkadir

I would create a chart source sheet, Number of Tasks per Person, with a Single Contact List column, "Person," and "Num of Tasks" column to calculate the number of tasks per person, i.e., contact list.

The formula is something like this;

• =COUNTIFS({Task Name}, ISTEXT(@cell), {Assigned To}, HAS(@cell, Person@row))
  • {Task Name}, ISTEXT(@cell) makes sure task name is not empty.
  • {Assigned To}, HAS(@cell, Person@row) checks if the range {Assinged To}'s each value has Person@row.
  •  {Assinged To} is a cross-sheet reference to the project sheet's Multiple Contact List.

Please take a look at the demo dashboard published here.

https://app.smartsheet.com/b/publish?EQBCT=0b78b1feb38c43458fb95521677a93f2

SebH

Hello @jmyzk_cloudsmart_jp

On that "chart source sheet", did you manually enter every email in the column "Persons" or did you use a formula?

I'd like to automate that, so if you know a formula, that would be great!

jmyzk_cloudsmart_jp

Hi @SebH

I did not use a formula.

If you want to use a formula, first, I would split the multiple contacts into columns of single contact text, using text functions like FIND, LEFT, MID, and SUBSTITUTE.

If you reference a multiple contacts cell as a text, it gives text like "contact1, contact2, contact 3 ...." you use the above text function to cut out each contact.

Then, at the "chart source sheet," add a column, "Index" or "No." to pre-populate numbers like 1, 2, 3,... 20, which you can use as the index function's row index.

The formula for the "Persons" column would be;

=IFERROR(INDEX(DISTINCT({range of columns of contat1, contact2, contact 3...}), Index@row),"")

It's a lot of work, especially splitting the multiple contacts part, so I manually enter emails.