ExcelNewbie2020
Active Member
- Joined
- Dec 3, 2020
- Messages
- 322
- Office Version
- 365
- Platform
- Windows
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
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. |