Summarize tasks for assigned teammate on an intake tracker

Hi there - I am not understanding how to use this COUNTIFS formula above. I am in the process of creating a SmartSheet dashboard for my team from an intake tracker we have. I would like to use the metrics widgets which require sheet summary formulas.

Problem - I am trying to create a sheet summary of how many current tasks are assigned to a specific resource - we have 9 teammates that are assigned to these tasks but the assigned column is by email and we have 5 different priority categories. is there any way I can get some help on the below formula on how to actually input it? Currently I am getting unsparseable

=COUNTIFS({Range - Assigned To}; Assigned@row; {Range - Status}; "In Progress")

where do I input the teammates email in this and the priorities? Status could work to for the example and I can reverse engineer.

it's an intake tracker with constant tickets being assigned to different teammates


  • Mike TV
    Mike TV ✭✭✭✭✭✭
    edited 06/24/22


    Try modifying it like this:

    =COUNTIFS({Range - Assigned To}, =Assigned@row, {Range - Status}, ="In Progress")

  • Rhett
    edited 06/24/22

    @Mike TV where do I plug in the email? Their name populates in multiple rows so would it be something like this?

    =COUNTIFS({Range - Assigned}; [email protected]; {range -status}; ="in progress")

    I could also be using the wrong formula

    I have 3 columns I am looking to pull information from: priority, status, and assigned

    Priority = p1, p2, p3, p4, p5

    Status = in progress, submitted, assigned, complete

    Assigned = [email protected] (this is dynamic as a new ticket comes in round robin for assignees)

    I am looking to make a sheet summary formula for how many P1 tasks are assigned to [email protected] that are in progress

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Rhett

    Yes, that's where you'd put the email, but you'd need to list it in quotes:

    =COUNTIFS({Range - Assigned}; = "[email protected]"; {range -status}; ="in progress")

    If you're looking to also count how many of these are "P1", you'll just need to add in an additional range and criteria, like so:

    =COUNTIFS({Range - Assigned}; = "[email protected]"; {range -status}; ="in progress"; {range - Priority); = "P1")

    If you're still getting an error, it would be helpful to see screen captures but please block out sensitive data.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!