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

 

[jaymisra]

I would wait and see if it's possible with the formula I know there is a slight creativity required to achieve the desired outcome but sure if it's not possible then will need to go down the VBA route

 

[bebo021999]

Without VBA, I think a solution with a helper column would be better:

Book1
	A	B	C	D	E
1	Week	Agent	Response	Helper column	3
2	46	Joe	error free	1
3	46	Joe	error free
4	46	Zara	error free	1
5	46	Laura	error free
6	46	Laura	Critical error
7	46	Zara	error free
8	46	Joe	error free
9	46	Joe	error free
10	46	Zara	error free
11	46	Laura	error free
12	46	John	error free	1
13	46	Alex	Critical error
Sheet1

Cell Formulas
Range	Formula
E1	E1	=SUM(D2:D13)
D2:D13	D2	=IF(C2="error free",IF(COUNTIF($B$2:B2,B2)=1,IF(COUNTIFS($B$2:$B$13,B2,$C$2:$C$13,"<>error free")=0,1,""),""),"")

 

[jaymisra]

Thanks I think while I was playing around I have found a solution which is working as expected

=SUM(IF(COUNTIFS($B$2:$B$13, UNIQUE(FILTER($B$2:$B$13, $C$2:$C$13 = "error free")), $C$2:$C$13, "<>error free") = 0, 1, 0))