Counting "First Pass" and Subsequent results

poor_cookie

New Member
Joined
Dec 16, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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?

1608184479976.png


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.
 

Some videos you may like

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
Joined
Sep 18, 2013
Messages
4,117
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top