COUNTIFS with cell reference

[errollflynn]

Hello everyone,

I have a spreadsheet with employee data that contains a formula that identifies instances of consecutive weeks worked. There is an IF statement with a true value of "Yes" when consecutive weeks are identified. As each row identifies a work week, most employees have multiple rows of data and I am trying to count the number of consecutive weeks per employee. I tried using COUNTIFS while referencing the column containing the unique key in the first screenshot and the same value, but specifically the cell, in the second screenshot. It is not working as I'm getting 0's. Any help would be appreciated.

Thank you

 

[kweaver]

Images are not very helpful in looking at possible solutions to your issue. Please use XL2BB to post a sample of your worksheet and expected results.

 

[errollflynn]

Thank you for the feedback. Please see below.

Delete Data.xlsx
	A	B	C	D	E	F	G
1	Key	Client#	Last_Name	Full Name	pay_period_start	pay_period_end	Consecutive
2	.8482	AEGP38	.	., DEX	04/03/2022	04/16/2022	Yes
3	.8482	AEGP38	.	., DEX	04/10/2022	04/23/2022	Yes
4	.8482	AEGP38	.	., DEX	04/17/2022	04/30/2022	Yes
5	.8482	AEGP38	.	., DEX	04/24/2022	04/30/2022	Yes
6	ABC6188	AEGP20	ABC	ABC, JOHN	01/02/2022	01/08/2022	Yes
7	ABC6188	AEGP20	ABC	ABC, JOHN	01/09/2022	01/15/2022	No
8	ABC6188	AEGP20	ABC	ABC, JOHN	01/23/2022	01/29/2022	No
9	ABC6188	AEGP20	ABC	ABC, JOHN	02/13/2022	02/19/2022	Yes
10	ABC6188	AEGP20	ABC	ABC, JOHN	02/20/2022	03/05/2022	No
11	ABC6188	AEGP20	ABC	ABC, JOHN	02/20/2022	03/05/2022	Yes
12	ABC6188	AEGP20	ABC	ABC, JOHN	02/27/2022	03/05/2022	Yes
13	ABC6188	AEGP20	ABC	ABC, JOHN	03/06/2022	03/19/2022	No
14	ABC6188	AEGP20	ABC	ABC, JOHN	03/20/2022	03/26/2022	No
15	ABC6188	AEGP20	ABC	ABC, JOHN	03/20/2022	03/26/2022	No
16	ABC6188	AEGP20	ABC	ABC, JOHN	04/10/2022	04/23/2022	No
17	ABC6188	AEGP20	ABC	ABC, JOHN	04/10/2022	04/23/2022	Yes
18	ABC6188	AEGP20	ABC	ABC, JOHN	04/17/2022	04/23/2022	No
19	ABC6188	AEGP20	ABC	ABC, JOHN	04/17/2022	04/23/2022	Yes
20	ABC6188	AEGP20	ABC	ABC, JOHN	04/24/2022	04/30/2022	Yes
21	ABC6188	AEGP20	ABC	ABC, JOHN	05/01/2022	05/14/2022	Yes
22	ABC6188	AEGP20	ABC	ABC, JOHN	05/08/2022	05/21/2022	No
23	ABC6188	AEGP20	ABC	ABC, JOHN	05/08/2022	05/21/2022	No
24	ABC6188	AEGP20	ABC	ABC, JOHN	05/08/2022	05/21/2022	No
25	ABC6188	AEGP20	ABC	ABC, JOHN	05/29/2022	06/04/2022	Yes
26	ABC6188	AEGP20	ABC	ABC, JOHN	06/05/2022	06/11/2022	No
27	ABC6188	AEGP20	ABC	ABC, JOHN	06/19/2022	06/25/2022	No
28	ABC6188	AEGP20	ABC	ABC, JOHN	07/10/2022	07/16/2022	No
29	ABC6188	AEGP20	ABC	ABC, JOHN	07/10/2022	07/16/2022	No
30	ABC6188	AEGP20	ABC	ABC, JOHN	08/21/2022	08/27/2022	No
31	ABC6188	AEGP20	ABC	ABC, JOHN	09/04/2022	09/10/2022	No
32	ABC6188	AEGP20	ABC	ABC, JOHN	09/18/2022	09/24/2022	Yes
33	ABC6188	AEGP20	ABC	ABC, JOHN	09/25/2022	10/01/2022	Yes
34	ABC6188	AEGP20	ABC	ABC, JOHN	10/02/2022	10/15/2022	Yes
35	ABC6188	AEGP20	ABC	ABC, JOHN	10/09/2022	10/15/2022	No
36	ABC6188	AEGP20	ABC	ABC, JOHN	10/23/2022	10/29/2022	Yes
37	ABC6188	AEGP20	ABC	ABC, JOHN	10/30/2022	11/05/2022	Yes
38	ABC6188	AEGP20	ABC	ABC, JOHN	11/06/2022	11/12/2022	Yes
39	ABC6188	AEGP20	ABC	ABC, JOHN	11/13/2022	11/19/2022	No
40	ABC6188	AEGP20	ABC	ABC, JOHN	11/13/2022	11/19/2022	No
Sheet1

Cell Formulas
Range	Formula
G2:G40	G2	=IF(E2=E3-7,"Yes", IF(AND(E1=E2-7,A2<>A3),"Yes","No"))

Delete Data.xlsx
	A	B	C	D
1	Key	Full Name	Weeks Worked	Consecutive Weeks
2	.8482	., DEX	4
3	ABC6188	ABC, JOHN	29
Sheet2

 

[kweaver]

Is the result counting the Yes values per unique name?
If not, why is ABC, JOHN 29?