sum function that will reset the running total every after 5 counts

[ExcelNewbie2020]

Cell J3 i have the counts of all the names in column B that reset every after 5 counts..
I need a formula in column K that will reset the running total per name after 5 counts..

For example in Name 1 its total count is 8, since it reset the count after 5 counts, it will only have the total counts of 3 and the running balance will be coming from the last 3 instances

07-07-24​	Name1​	41​
09-07-24​	Name1​	19​
10-07-24​	Name1​	32​

Book3
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Name	Amount								Formula here
2	01-07-24	Name1	16						name	Count	Amount
3	01-07-24	Name2	35						Name1	3	92
4	01-07-24	Name3	23						Name2	3	108
5	01-07-24	Name4	44						Name3	3	43
6	01-07-24	Name5	15						Name4	4	87
7	01-07-24	Name6	19						Name5	4	114
8	01-07-24	Name7	22						Name6	3	94
9	01-07-24	Name8	16						Name7	2	54
10	01-07-24	Name9	24						Name8	4	85
11	01-07-24	Name10	16						Name9	5	133
12	02-07-24	Name1	45						Name10	3	97
13	02-07-24	Name2	19
14	02-07-24	Name3	50
15	02-07-24	Name4	37
16	02-07-24	Name5	23
17	02-07-24	Name6	46
18	03-07-24	Name2	38
19	03-07-24	Name3	38
20	03-07-24	Name4	29
21	03-07-24	Name5	26
22	03-07-24	Name6	14
23	03-07-24	Name7	27
24	03-07-24	Name10	36
25	04-07-24	Name1	44
26	04-07-24	Name2	10
27	04-07-24	Name3	27
28	04-07-24	Name4	11
29	04-07-24	Name5	21
30	04-07-24	Name6	36
31	04-07-24	Name7	21
32	04-07-24	Name8	46
33	04-07-24	Name9	27
34	04-07-24	Name10	45
35	05-07-24	Name1	43
36	05-07-24	Name2	31
37	05-07-24	Name3	32
38	05-07-24	Name4	42
39	05-07-24	Name5	12
40	05-07-24	Name6	26
41	05-07-24	Name7	45
42	05-07-24	Name9	31
43	06-07-24	Name4	28
44	06-07-24	Name5	43
45	06-07-24	Name6	31
46	06-07-24	Name7	40
47	06-07-24	Name8	11
48	06-07-24	Name9	15
49	06-07-24	Name1	12
50	07-07-24	Name1	41
51	07-07-24	Name2	23
52	07-07-24	Name3	10
53	07-07-24	Name4	10
54	07-07-24	Name5	15
55	07-07-24	Name6	41
56	07-07-24	Name7	28
57	07-07-24	Name9	36
58	08-07-24	Name6	22
59	08-07-24	Name7	26
60	08-07-24	Name8	12
61	09-07-24	Name1	19
62	09-07-24	Name2	37
63	09-07-24	Name3	11
64	09-07-24	Name4	35
65	09-07-24	Name5	23
66	10-07-24	Name1	32
67	10-07-24	Name2	48
68	10-07-24	Name3	22
69	10-07-24	Name4	14
70	10-07-24	Name5	33
Sheet7

Cell Formulas
Range	Formula
I3:I12	I3	=UNIQUE(B2:B70)
J3:J12	J3	=IF(MOD(COUNTIF($B$2:$B$100,UNIQUE(B2:B70)),5)=0,5,MOD(COUNTIF($B$2:$B$100,UNIQUE(B2:B70)),5))
Dynamic array formulas.

 

[Cubist]

Try:

=SUM(TAKE(FILTER($C$2:$C$70,$B$2:$B$70=I3),-J3))

You can use LET for the count in J:

=LET(m,MOD(COUNTIF(B2:B70,I3#),5),IF(m,m,5))

 

[ExcelNewbie2020]

Try:

=SUM(TAKE(FILTER($C$2:$C$70,$B$2:$B$70=I3),-J3))

You can use LET for the count in J:

=LET(m,MOD(COUNTIF(B2:B70,I3#),5),IF(m,m,5))

that worked..i make it complicated by thinking of resetting it when i can just take the last instances.. thanks man, really appreciated

 

[Cubist]

thanks man, really appreciated

You're welcome.