Track a "streak" for sequential records based on name, date and status

Question

In Google Sheets, I would like to create a numerical column which tracks the "streak" of sequential records. This would be person-specific based on a name column, whether or not the records are sequential would be determined by a date column, and whether or not the streak is added to would be based on a status column. The contents of the status column are either "Complete", in which case the streak should be added to +1, or "Failed", in which case the streak should be reset to 0.

Example where the Streak column is what I am looking to automate with formula:

Date	Name	Status	Streak
21/10/2024	John	Approved	1
21/10/2024	Joe	Approved	1
22/10/2024	Jamie	Rejected	0
22/10/2024	Jay	Approved	1
22/10/2024	Joe	Approved	2
22/10/2024	Joe	Rejected	0
22/10/2024	Jamie	Approved	1
22/10/2024	John	Approved	2
22/10/2024	John	Approved	3
22/10/2024	Joe	Approved	1
23/10/2024	Jay	Approved	2
23/10/2024	Joe	Rejected	0
23/10/2024	Joe	Approved	1
23/10/2024	Jamie	Approved	2
23/10/2024	Joe	Approved	2
23/10/2024	Jamie	Rejected	0
23/10/2024	Joe	Approved	3
24/10/2024	Jay	Approved	3
24/10/2024	Jay	Approved	4
24/10/2024	Joe	Rejected	0

I have tried looking up similar issues and modifying those formulas with no success, as the only results I have found seem to involve grid arrays whereas I am looking to create a column which tracks a streak.

Answer 1

You may try:

=map(B2:B,C2:C,lambda(Σ,Λ,if(Σ="",,reduce(,filter(C2:Λ,B2:Σ=Σ),lambda(a,c,if(c="Rejected",0,a+1))))))