How to count the times a person is assigned to a defect status

bstevens · 03/21/22

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.

Genevieve P. · 03/22/22

Hi @bstevens

1 - Do you know the set list of users it could be Assigned To? I would suggest having this "Assign To" column be a manual one where you list out each person.

You could JOIN together all the individual, distinct values into one cell up at the top for a summary, but parsing out data down multiple rows becomes trickier.

I would personally use a formula to Count how many Distinct Values are in the source sheet and compare it to the number of rows in this current sheet to make sure I haven't missed anyone.

2 - It looks like you already have the COUNTIF part set up! To include in the user you're looking for, you'd just need to change it to be COUNTIFS (plural), and then find the Assign To column in the other sheet and look for [Assigned To]@row.

Cheers,

Genevieve

bstevens · 03/22/22

Thanks the feedback. I was able to resolve by creating multiple reports that would then feed the dashboard. After playing around with the reports I was able to capture the summary by person I needed. The issue with creating a list of user names is we will not know who all will be testing from project to project.

Good catch on the COUNTIFS error.

How to count the times a person is assigned to a defect status

Answers

Help Article Resources

Categories