# Counting "First Pass" and Subsequent results

##### New Member
Hi, I would like to count the number of the first passes based on a list of IDs. What formula can i write to calculate the sum of all unique first pass IDs?

i.e: ID 1 with PASS (Row 2 ) is a first Pass.
ID 2 with PASS (Row 3) is a first Pass.
ID 2 with a PASS (Row 4) is not a first Pass.
ID 4 with a PASS (Row 10) is not a first Pass - because it failed in Row 8 and 9.

From the table, the number should be: 2 (Only Row 2 and Row 3 are unique first Pass IDs)

The additional complexity is : After identifying the unique first pass IDs, I would like to identify the unique IDs for subsequent passes.
i.e.:
ID 2 with a PASS (Row 4) is not a subsequent pass as there is already a first past earlier.
ID 4 with a PASS (Row 10) is considered as a subsequent pass, as the earlier Statuses for this ID are FAIL.

This is pretty challenging for me as a beginner. Many thanks for the help.

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### StephenCrump

##### MrExcel MVP
Only lightly tested, but I think this works:

ABC
1IDStatus
21PASS
32PASS
42PASS
52FAIL
63FAIL
73FAIL
84FAIL
94FAIL
104PASS
11
12First PASS2
13Subsequent PASS1
Sheet1
Cell Formulas
RangeFormula
C12C12=SUMPRODUCT((MATCH(A2:A10,A2:A10,)=(ROW(A2:A10)-ROW(A2)+1))*(B2:B10="PASS"))
C13C13=SUMPRODUCT(--(IFERROR(MATCH(A2:A10&"PASS",A2:A10&B2:B10,)=ROW(A2:A10)-ROW(A2)+1,0)))-C12

Replies
3
Views
121
Replies
2
Views
103
Replies
2
Views
170
Replies
2
Views
823
Replies
16
Views
300

1,127,420
Messages
5,624,688
Members
416,040
Latest member
patriciocabello

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back