Show count of work orders not marked as resolved *and* assigned to a specific resource

bc_tc

Hi all, apologies for my lack of understanding here. I'm having a hard time wrapping my head around formulas enough to create something to accomplish this task.

In a "Work Orders" sheet, there are two specific columns that I'm trying to pull from - "Status" and "Assigned to". On another sheet called "Primary roll-up", I want to reference the "Work Orders" sheet, and spit out a number showing the number of work orders assigned to "Ben" (in the "Assigned to" column from the "Work Orders" sheet), but only the work orders not marked as resolved (in the "Status" column from the "Work Orders" sheet).

How would I go about this? I've tried using COUNTIF, INDEX + MATCH, and VLOOKUP, but can't seem to get the formula right.

Any help would be greatly appreciated 🙂

Paul Newcome

You would only need a COUNTIFS function.

=COUNTIFS({Status Column}, @cell <> "Completed", {Assigned To Column}, @cell = "Ben")