Multiple criteria needed for a COUNTIFS statement

I'm trying to calculate how many Open Tickets are currently assigned to each one of our team members. (See attached screenshot titled "Master Sheet")

-A Ticket is a row in our sheet and has a value in the "Request ID" column

-An Open Ticket is defined by the column "Ticket Status", with a value of "Not Started", "In Progress" or "On Hold".

-Assigned to is defined by column "Assigned To" which is a multi select Contact List field. I want to count the ticket for each member it is assigned to. i.e. if the ticket has 2 contacts listed, I want the ticket to be counted 1x for Contact A and a 2nd time for Contact B. That way they both get credit.

I've tried multiple different ways of doing this, and keep getting errors. I've tried COUNTIFS using a CONTAINS (for the contact list field) and OR statement (to list multiple ticket statuses). This doesn't seem to be working.

My end goal is to have a summary of "Open tickets X Team member" graph in a separate sheet (See attached screenshot titled "End Goal")

Thank you in advance for any help!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!