Counting number of tasks per Assigned To contact

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.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!