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

edited 11/11/22 in Formulas and Functions

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 🙂


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!