Formula to get goal status

[zinah]

Hi,

I have a list of employees with their goals, some of those employees have no goals, some have one goal and others have multiple goals. What I need to determine whom of those employees have goals and who don't. The challenge is some of employees have multiple goals and some have EEID but blank goal, so how can I figure out the total employees (unique EEID) that has goals, and don't have goals?
The result in Goal Status will show if any employee at least has one goal, then "Yes", if not, then "No"

EEID​	Goal​	Goal Status​
679693​	AAA​
679693​	​
679693​	CCC​
679693​	DDD​
679693​	EEE​
155434​	BBB​
734814​	CCC​
838080​
525294​	CCC​
525294​	AAA​
890277​
511806​	AAA​
511806​	BBB​
511806​	EEE​
437962​	AAA​
854101​	AAA​
659905​	CCC​
659905​	DDD​
659905​	EEE​
659905​	FFF​
659905​	BBB​
706506​	AAA​
426276​
176939​
177085​	AAA​
173545​	CCC​
484602​	BBB​
731301​	FFF​
731301​	EEE​
731301​	DDD​
731301​	AAA​
731301​	CCC​
771349​	DDD​
379818​	DDD​
958328​
873892​	EEE​
110947​	AAA​
483678​
159304​	DDD​

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C
1	EEID	Goal	Goal Status
2	679693	AAA	Yes
3	679693		Yes
4	679693	CCC	Yes
5	679693	DDD	Yes
6	679693	EEE	Yes
7	155434	BBB	Yes
8	734814	CCC	Yes
9	838080		No
10	525294		No
11	525294		No
12	890277		No
13	511806	AAA	Yes
14	511806	BBB	Yes
15	511806	EEE	Yes
16	437962	AAA	Yes
17	854101	AAA	Yes
18	659905	CCC	Yes
19	659905	DDD	Yes
20	659905	EEE	Yes
21	659905	FFF	Yes
22	659905	BBB	Yes
23	706506	AAA	Yes
24	426276		No
25	176939		No
26	177085	AAA	Yes
27	173545	CCC	Yes
28	484602	BBB	Yes
29	731301	FFF	Yes
30	731301	EEE	Yes
31	731301	DDD	Yes
32	731301	AAA	Yes
33	731301	CCC	Yes
34	771349	DDD	Yes
35	379818	DDD	Yes
36	958328		No
37	873892	EEE	Yes
38	110947	AAA	Yes
39	483678		No
40	159304	DDD	Yes
Main

Cell Formulas
Range	Formula
C2:C40	C2	=IF(COUNT(FILTER($A$2:$A$40,($A$2:$A$40=A2)*($B$2:$B$40<>""))),"Yes","No")

 

[RicoS]

Assuming your table starts in A1:

=IF(COUNTIFS($A$2:$A$40,A2,$B$2:$B$40,B2)=0,"No","Yes")

Or if you format as a table:

=IF(COUNTIFS([EEID],[@EEID],[Goal],[@Goal])=0,"No","Yes")

 

[jtakw]

Hi,

You can also try this:

Book3.xlsx
	A	B	C	D
1	EEID	Goal	Goal Status	# of Goals
2	679693	AAA	Yes	4
3	679693		Yes
4	679693	CCC	Yes
5	679693	DDD	Yes
6	679693	EEE	Yes
7	155434	BBB	Yes	1
8	734814	CCC	Yes	1
9	838080		No	0
10	525294	CCC	Yes	2
11	525294	AAA	Yes
12	890277		No	0
13	511806	AAA	Yes	3
14	511806	BBB	Yes
15	511806	EEE	Yes
16	437962	AAA	Yes	1
17	854101	AAA	Yes	1
18	659905	CCC	Yes	5
19	659905	DDD	Yes
20	659905	EEE	Yes
21	659905	FFF	Yes
22	659905	BBB	Yes
23	706506	AAA	Yes	1
24	426276		No	0
25	176939		No	0
26	177085	AAA	Yes	1
27	173545	CCC	Yes	1
28	484602	BBB	Yes	1
29	731301	FFF	Yes	5
30	731301	EEE	Yes
31	731301	DDD	Yes
32	731301	AAA	Yes
33	731301	CCC	Yes
34	771349	DDD	Yes	1
35	379818	DDD	Yes	1
36	958328		No	0
37	873892	EEE	Yes	1
38	110947	AAA	Yes	1
39	483678		No	0
40	159304	DDD	Yes	1
Sheet786

Cell Formulas
Range	Formula
C2:C40	C2	=IF(COUNTIFS(A$2:A$50,A2,B$2:B$50,"<>"),"Yes","No")
D2:D40	D2	=IF(COUNTIF(A$2:A2,A2)=1,COUNTIFS(A$2:A$50,A2,B$2:B$50,"<>"),"")

Edit: Added # of Goals (Column D)