Count how many unique Operations Sequences have been completed

[VBE313]

How can I count Operations Complete in this scenario? I need to get the unique Completed Operation Sequences per Name (Column H). I don't know what formula I could use in H2. Does anyone have any ideas how I could count unique values that only have "Completed" in the 'Completed?' column? The formulas in F2,F3 and G2 are in a table below the data table.

Name	Qty	Operation Sequence	Completed?	Completed Qty	First of Name?	Total Operations
Ethan	40	5	Completed	20	Yes	4
Ethan	40	5	Completed	10
Ethan	40	5	Completed	5
Ethan	40	5	Completed	5
Ethan	40	10	Completed	10
Ethan	40	10	Completed	10
Ethan	40	10	Completed	20
Ethan	40	15	Completed	8
Ethan	40	15	Completed	8
Ethan	40	15	Completed	8
Ethan	40	15	Completed	8
Ethan	40	15	Completed	8
Ethan	40	20	Completed	20
Ethan	40	20	Completed	20
Emily	30	5	Completed	10	Yes	6
Emily	30	5	Completed	5
Emily	30	5	Completed	5
Emily	30	5	Completed	10
Emily	30	10
Emily	30	20
Emily	30	30
Emily	30	40
Emily	30	50

Formula in G2	=IF([@[First of Name?]]="Yes",COUNTA(UNIQUE(FILTER([Operation Sequence],[Name]=[@Name]))),"")
Formula in F2	=IF(COUNTIF(A2:$A$2,A2)=1,"Yes","")
Formula in F3	=IF(COUNTIF(A$2:$A3,A3)=1,"Yes","")

 

[Fluff]

Maybe

=IF([@[First of Name?]]="Yes",COUNTA(UNIQUE(FILTER([Operation Sequence],([Name]=[@Name])*([Completed?]="Completed")))),"")

 

[JamesCanale]

This works if a specific sequence number is all the same. So if there are four '5's, rows 2-5, they are all listed as completed. If not, it'll be a problem. I don't know why at this point.

MrExcelPlayground.xlsm
	A	B	C	D	E	F	G	H
1	Name	Qty	Operation Sequence	Completed?	Completed Qty	First of Name?	Total Operations	Completed Unique Ops
2	Ethan	40	5	Completed	20	Yes	4	4
3	Ethan	40	5	Completed	10
4	Ethan	40	5	Completed	5
5	Ethan	40	5	Completed	5
6	Ethan	40	10	Completed	10
7	Ethan	40	10	Completed	10
8	Ethan	40	10	Completed	20
9	Ethan	40	15	Completed	8
10	Ethan	40	15	Completed	8
11	Ethan	40	15	Completed	8
12	Ethan	40	15	Completed	8
13	Ethan	40	15	Completed	8
14	Ethan	40	20	Completed	20
15	Ethan	40	20	Completed	20
16	Emily	30	5	Completed	10	Yes	6	1
17	Emily	30	5	Completed	5
18	Emily	30	5	Completed	5
19	Emily	30	5	Completed	10
20	Emily	30	10
21	Emily	30	20
22	Emily	30	30
23	Emily	30	40
24	Emily	30	50
Sheet30

Cell Formulas
Range	Formula
F2	F2	=IF(COUNTIF(A2:$A$2,A2)=1,"Yes","")
G2:G24	G2	=IF(F2="Yes",COUNTA(UNIQUE(FILTER(C2:C24,A2:A24=A2))),"")
H2:H24	H2	=IF(F2="Yes",SUMPRODUCT(--($A$2:$A$24=A2),--($D$2:$D$24="Completed"),--($C$2:$C$24<>OFFSET($C$2:$C$24,1,0))),"")
F3:F24	F3	=IF(COUNTIF(A$2:$A3,A3)=1,"Yes","")