Consecutive week count with multiple conditions

[Asuka]

Hi,

I am after some help to count the number of consecutive weeks an individual has been paid. Difficulties with the data set are the duplicates values. I have explored and trialled countifs but haven't been able to incorporate the portion around a consecutive count.

I've included an example dataset for reference:

In this example set, the output should be:
John Doe: 0 or blank
Jane Roe: 2
Tom Lee: 3

Name	Payment Period
John Doe	01/07/2020
John Doe	15/07/2020
Jane Roe	01/07/2020
Jane Roe	01/07/2020
Jane Roe	08/07/2020
Jane Roe	08/07/2020
Jane Roe	15/07/2020
Jane Roe	15/07/2020
Tom Lee	08/07/2020
Tom Lee	08/07/2020
Tom Lee	08/07/2020
Tom Lee	15/07/2020
Tom Lee	15/07/2020
Tom Lee	22/07/2020
Tom Lee	29/07/2020

Thank you!

 

[Asuka]

Sorry Jane Roe's output should be 3.

 

[Asuka]

Sorry Jane Roe's output should be 3.

Ignore.

 

[Anonymous1378]

Try this

Book23
	A	B	C	D	E
1	Name	Payment Period
2	John Doe	01-07-20		John Doe	0
3	John Doe	15-07-20		Jane Roe	2
4	Jane Roe	01-07-20		Tom Lee	3
5	Jane Roe	01-07-20
6	Jane Roe	08-07-20
7	Jane Roe	08-07-20
8	Jane Roe	15-07-20
9	Jane Roe	15-07-20
10	Tom Lee	08-07-20
11	Tom Lee	08-07-20
12	Tom Lee	08-07-20
13	Tom Lee	15-07-20
14	Tom Lee	15-07-20
15	Tom Lee	22-07-20
16	Tom Lee	29-07-20
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=UNIQUE(A2:A16)
E2:E4	E2	=LET(_a,FILTER($B$2:$B$16,$A$2:$A$16=D2),_b,MAX(FREQUENCY(_a,_a)),IF(_b=1,0,_b))
Dynamic array formulas.

 

[Asuka]

Try this

Book23
	A	B	C	D	E
1	Name	Payment Period
2	John Doe	01-07-20		John Doe	0
3	John Doe	15-07-20		Jane Roe	2
4	Jane Roe	01-07-20		Tom Lee	3
5	Jane Roe	01-07-20
6	Jane Roe	08-07-20
7	Jane Roe	08-07-20
8	Jane Roe	15-07-20
9	Jane Roe	15-07-20
10	Tom Lee	08-07-20
11	Tom Lee	08-07-20
12	Tom Lee	08-07-20
13	Tom Lee	15-07-20
14	Tom Lee	15-07-20
15	Tom Lee	22-07-20
16	Tom Lee	29-07-20
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=UNIQUE(A2:A16)
E2:E4	E2	=LET(_a,FILTER($B$2:$B$16,$A$2:$A$16=D2),_b,MAX(FREQUENCY(_a,_a)),IF(_b=1,0,_b))
Dynamic array formulas.

Thank you - This works! Sorry to toss another spanner in the works.. is there a way to have this done in one formula? Something similar to the below?

Name	Payment Period	Consecutive week count
John Doe	01/07/2020	0
John Doe	15/07/2020	0
Jane Roe	01/07/2020	0
Jane Roe	01/07/2020	0
Jane Roe	08/07/2020	1
Jane Roe	08/07/2020	1
Jane Roe	15/07/2020	2
Jane Roe	15/07/2020	2
Tom Lee	08/07/2020	0
Tom Lee	08/07/2020	0
Tom Lee	08/07/2020	0
Tom Lee	15/07/2020	1
Tom Lee	15/07/2020	1
Tom Lee	22/07/2020	2
Tom Lee	29/07/2020	3

 

[Anonymous1378]

Oh, that was completely different from my last interpretation of your data.

Try this

Book57
	A	B	C
1	Name	Payment Period	Consecutive week count
2	John Doe	01-07-20	0
3	John Doe	15-07-20	0
4	Jane Roe	01-07-20	0
5	Jane Roe	01-07-20	0
6	Jane Roe	08-07-20	1
7	Jane Roe	08-07-20	1
8	Jane Roe	15-07-20	2
9	Jane Roe	15-07-20	2
10	Tom Lee	08-07-20	0
11	Tom Lee	08-07-20	0
12	Tom Lee	08-07-20	0
13	Tom Lee	15-07-20	1
14	Tom Lee	15-07-20	1
15	Tom Lee	22-07-20	2
16	Tom Lee	29-07-20	3
Sheet1

Cell Formulas
Range	Formula
C2:C16	C2	=IFERROR(XLOOKUP(A2&B2-7,A$1:A1&B$1:B1,C$1:C1,0),0)+SIGN(COUNTIFS(A$1:A1,A2,B$1:B1,B2-7))

 

[Asuka]

Sorry, that was my fault - I wasn't specific enough. This is perfect, exactly what I am after! Thank you so much for troubleshooting.

 

[Phuoc]

Or try this:

Book1
	A	B	C
1	Name	Payment Period	Consecutive week count
2	John Doe	01/07/2020	0
3	John Doe	15/07/2020	0
4	Jane Roe	01/07/2020	0
5	Jane Roe	01/07/2020	0
6	Jane Roe	08/07/2020	1
7	Jane Roe	08/07/2020	1
8	Jane Roe	15/07/2020	2
9	Jane Roe	15/07/2020	2
10	Tom Lee	08/07/2020	0
11	Tom Lee	08/07/2020	0
12	Tom Lee	08/07/2020	0
13	Tom Lee	15/07/2020	1
14	Tom Lee	15/07/2020	1
15	Tom Lee	22/07/2020	2
16	Tom Lee	29/07/2020	3
Sheet1

Cell Formulas
Range	Formula
C2:C16	C2	=IF(A2<>A1,0,IF(B2-7=B1,C1+1,C1))

 

[Anonymous1378]

Oops, I forgot part way through that my formula can deal with unsorted data, so there was no need for absolute references.

=IFERROR(XLOOKUP(A2&B2-7,A:A&B:B,C:C,0),0)+SIGN(COUNTIFS(A:A,A2,B:B,B2-7))