Count unique agent names based on the criteria

[jaymisra]

Hi Guys,

I am struggling to get the unique agent counts for the data set I have. Need your help

The table contains week number, agent name and survey outcome. I want to know unique count of agents who have error free outcome. As you can see from the data an agent can have possible outcomes as error free, critical error, non critical error. As you can see both Zara and Laura has error free responses so the count ll be 2. What will be formula to achieve that. Thanks

 

[Michael M]

MAybe this way
change the ranges to suit your requirements

=SUM(--(LEN(UNIQUE(FILTER($B$2:$B$11,$C$2:$C$11="error free","")))>0))

 

[jaymisra]

Thanks Michael

getting this error Error filter has mismatched range sizes. Expected row count 10: column count: 1. Actual row count: 1,column count: 1

 

[kevin9999]

Possibility?

Book1
	A	B	C	D	E
1	Week	Agent	Response
2	46	Joe	error free	Count:	2
3	46	Joe	error free
4	46	Zara	error free
5	46	Laura	error free
6	46	Laura	error free
7	46	Zara	error free
8	46	Joe	Critical error
9	46	Joe	non-critical error
10	46	Zara	error free
11	46	Laura	error free
Sheet1

Cell Formulas
Range	Formula
E2	E2	=ROWS(UNIQUE(FILTER($B$2:$B$11,($C$2:$C$11="error free"))))-ROWS(UNIQUE(FILTER($B$2:$B$11,($C$2:$C$11="Critical error")+($C$2:$C$11="non-critical error"))))

 

[jaymisra]

Thanks Kevin

It is working in an ideal scenario where you would expect to have possible mixture of the outcome but when we have scenarios like attached it gives the incorrect answers.

Is there a way to look at agent name and there outcomes and if it has all error free responses count that agent ONLY. See attached with 2 different scenarios. Thanks

 

[kevin9999]

There's probably a more elegant solution, but try this out:

Book1
	A	B	C	D
1	Week	Agent	Response
2	46	Joe	error free
3	46	Joe	error free
4	46	Zara	error free
5	46	Laura	error free
6	46	Laura	error free
7	46	Zara	error free
8	46	Joe	error free
9	46	Joe	error free
10	46	Zara	error free	5
11	46	Laura	error free
12	46	John	error free
13	46	Alex	error free
14
15	Week	Agent	Response
16	46	Joe	error free
17	46	Joe	error free
18	46	Zara	error free
19	46	Laura	error free
20	46	Laura	Critical error
21	46	Zara	error free
22	46	Joe	error free
23	46	Joe	error free
24	46	Zara	error free	2
25	46	Laura	error free
26	46	John	error free
27	46	Alex	Critical error
28
Sheet1

Cell Formulas
Range	Formula
D10	D10	=IFERROR(ROWS(UNIQUE(FILTER($B$2:$B$13,($C$2:$C$13="error free")))),0)-IFERROR(ROWS(UNIQUE(FILTER($B$2:$B$13,($C$2:$C$13="Critical error")+($C$2:$C$13="non-critical error")))),0)
D24	D24	=IFERROR(ROWS(UNIQUE(FILTER($B$16:$B$27,($C$16:$C$27="error free")))),0)-IFERROR(ROWS(UNIQUE(FILTER($B$16:$B$27,($C$16:$C$27="Critical error")+($C$16:$C$27="non-critical error")))),0)

 

[jaymisra]

It looks like second scenerio should have had 3 as the unique count of agents with error free responses but it returned 2. Is there any work around where it addresses all possible outcomes and then counts Only unique agents with error free responses. Thanks again

 

[kevin9999]

Sorry, I've tried all sorts of combinations, including:

=IFERROR(ROWS(UNIQUE(FILTER($B$2:$B$13,ISNUMBER(SEARCH(" e",$C$2:$C$13))=FALSE))),0)
=IFERROR(ROWS(UNIQUE(FILTER($B$16:$B$27,ISNUMBER(SEARCH(" e",$C$16:$C$27))=FALSE))),0)-IFERROR(ROWS(UNIQUE(FILTER($B$16:$B$27,ISNUMBER(SEARCH(" e",$C$16:$C$27))=TRUE))),0)
=COUNTA(UNIQUE(FILTER(B16:B27,ISNUMBER(SEARCH(" e",C16:C27))=FALSE)))

all to no avail. I'll have to leave this one to the formula experts

 

[jaymisra]

No problem. Thank you for trying though. Appreciated

 

[bebo021999]

Is an UDF (VBA code) OK for you?