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
Check out the Formula Handbook template!