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")

End Goal.png

Master Sheet.png

Thank you in advance for any help!

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

You were probably pretty close with your CONTAINS except for the fact that CONTAINS doesn't work with contact type columns. You have to use either a HAS or a FIND function.

=COUNTIFS({Assigned To}, HAS(@cell, "John Doe"), {Status}, @cell <> "Closed")

Kelly Henson

Thanks Paul. I'm getting an #UNPARSEABLE error. Any idea what may be happening?

Paul Newcome

@Kelly Henson It look like you have an extra set of {curly brackets} that needs removed from around your first range.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.