Counting number of tasks per Assigned To contact

Sabdelkadir
Sabdelkadir ✭✭
edited 03/25/24 in Formulas and Functions

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.

Answers

  • jmyzk_cloudsmart_jp
    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.


  • 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
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!