How to count the times a person is assigned to a defect status
Objective: Have dashboard that reflects defect status and who is assigned to it, if blank then "Not Assigned" Example 1 Jake has three defects assigned to him. 2 = "Intake" 1 = "Vendor In Process"
Example 2 Eric has 5 assigned to him 1 = "Resolved"; 3 = "Ready for Retest"; 1 = "In Process";
For any defects not assigned, that are not assigned then I would like a row for Not Assigned and the status (usually would expect for New defects not yet reviewed).
I have created a sheet with Columns (Assigned To, Intake, DS Review, Vendor Review, etc). At first I tried using the existing Contact List column (Assigned To) but could not get formulas to work. Then I created a Helper column to convert the Assigned To contact list to a Text/Number column. I would hide this if it is required. Otherwise prefer not to add another column.
Formula part 1: In the Assigned To I want to find all unique names (I tried DISTINCT formula with no success); Formula part 2: Once I can correctly populate the Assigned To column I would want my formula (COUNTIF) I am assuming unless better option, to then populate the Status count based on the Assigned To name.
This data will then be used to present the HR leadership QA dashboard.
Side note: I inherited this project and will be changing and simplifying the status options for future projects. I did not want to change the team flow mid-stream.