Every 10 dates should count and sum end of every 10th date

[vishu]

Hi,
Column A is Date column where each date/day product list is calculated end of the day in Column F.
Now in Column G need your help i.e. Every end of 1 to 10th, 11 to 20, 21 to 30th/31st (in Month of Feb 28th or 29th) Date need Total Amount
Please help me please

A	B	C	D	E	F	G
	Item	Qty	Rate	Amt
01-Apr-24	Milk	15.5	50	775
	Curd	3	75	225	1000
02-Apr-24	Milk	8	50	400
	Paneer	3	300	900
	Curd	3	75	225
	Khava	1	300	300	1825
03-Apr-24	Milk	12	50	600
	Paneer	3	300	900
	Amul Butter	1	170	170	1670
04-Apr-24	Milk	6	50	300
	Paneer	3	300	900
	Curd	5	75	375
	Khava	1	300	300	1875
05-Apr-24	Milk	10	50	500	500
06-Apr-24	Milk	12	50	600
	Milk	15.5	50	775
	Curd	3	75	225	1600
07-Apr-24	Milk	8	50	400
	Paneer	3	300	900	1300
08-Apr-24	Curd	3	75	225
	Khava	1	300	300
	Milk	12	50	600	1125
09-Apr-24	Paneer	3	300	900
	Amul Butter	1	170	170
	Milk	6	50	300
	Paneer	3	300	900	2270
10-Apr-24	Curd	5	75	375
	Khava	1	300	300
	Milk	10	50	500
	Milk	12	50	600	1775	14940
11-Apr-24	Milk	12	50	600
	Milk	15.5	50	775
	Curd	3	75	225
	Milk	8	50	400	2000
12-Apr-24	Paneer	3	300	900
	Curd	3	75	225
	Khava	1	300	300
	Milk	12	50	600
	Paneer	3	300	900	2925
13-Apr-24	Amul Butter	1	170	170
	Milk	6	50	300
	Paneer	3	300	900
	Curd	5	75	375
	Milk	12	50	600	2345
14-Apr-24	Milk	15.5	50	775
	Curd	3	75	225
	Milk	8	50	400
	Paneer	3	300	900
	Curd	3	75	225
	Khava	1	300	300	2825
15-Apr-24	Milk	12	50	600
	Paneer	3	300	900
	Milk	12	50	600	2100
16-Apr-24	Milk	12	50	600
	Curd	3	75	225	825
17-Apr-24	Milk	8	50	400
	Paneer	3	300	900	1300
18-Apr-24	Curd	3	75	225
	Khava	1	300	300
	Milk	12	50	600	1125
19-Apr-24	Paneer	3	300	900
	Amul Butter	1	170	170
	Milk	6	50	300	1370
20-Apr-24	Paneer	3	300	900
	Curd	5	75	375
	Milk	12	50	600	1875	18690
21-Apr-24	Milk	15.5	50	775

 

[Snakehips]

@vishu Does this help?

Testing Formula Update.xlsm
	A	B	C	D	E	F	G	H
1			Item	Qty	Rate	Amt
2		1-Apr-24	Milk	15.5	50	775
3			Curd	3	75	225	1000
4		2-Apr-24	Milk	8	50	400
5			Paneer	3	300	900
6			Curd	3	75	225
7			Khava	1	300	300	1825
8		3-Apr-24	Milk	12	50	600
9			Paneer	3	300	900
10			Amul Butter	1	170	170	1670
11		4-Apr-24	Milk	6	50	300
12			Paneer	3	300	900
13			Curd	5	75	375
14			Khava	1	300	300	1875
15		5-Apr-24	Milk	10	50	500	500
16		6-Apr-24	Milk	12	50	600
17			Milk	15.5	50	775
18			Curd	3	75	225	1600
19		7-Apr-24	Milk	8	50	400
20			Paneer	3	300	900	1300
21		8-Apr-24	Curd	3	75	225
22			Khava	1	300	300
23			Milk	12	50	600	1125
24		9-Apr-24	Paneer	3	300	900
25			Amul Butter	1	170	170
26			Milk	6	50	300
27			Paneer	3	300	900	2270
28		10-Apr-24	Curd	5	75	375
29			Khava	1	300	300
30			Milk	10	50	500
31			Milk	12	50	600	1775	14940
32		11-Apr-24	Milk	12	50	600
33			Milk	15.5	50	775
34			Curd	3	75	225
35			Milk	8	50	400	2000
36		12-Apr-24	Paneer	3	300	900
37			Curd	3	75	225
38			Khava	1	300	300
39			Milk	12	50	600
40			Paneer	3	300	900	2925
41		13-Apr-24	Amul Butter	1	170	170
42			Milk	6	50	300
43			Paneer	3	300	900
44			Curd	5	75	375
45			Milk	12	50	600	2345
46		14-Apr-24	Milk	15.5	50	775
47			Curd	3	75	225
48			Milk	8	50	400
49			Paneer	3	300	900
50			Curd	3	75	225
51			Khava	1	300	300	2825
52		15-Apr-24	Milk	12	50	600
53			Paneer	3	300	900
54			Milk	12	50	600	2100
55		16-Apr-24	Milk	12	50	600
56			Curd	3	75	225	825
57		17-Apr-24	Milk	8	50	400
58			Paneer	3	300	900	1300
59		18-Apr-24	Curd	3	75	225
60			Khava	1	300	300
61			Milk	12	50	600	1125
62		19-Apr-24	Paneer	3	300	900
63			Amul Butter	1	170	170
64			Milk	6	50	300	1370
65		20-Apr-24	Paneer	3	300	900
66			Curd	5	75	375
67			Milk	12	50	600	1875	18690
68		21-Apr-24	Milk	15.5	50	775
Sheet1

Cell Formulas
Range	Formula
H2:H3	H2	=IFERROR(IF(OR(DAY(B3)=11,DAY(B3)=21,DAY(B3)=1),SUM(G2:G$3)-SUM(H1:H$2),""),"")
H4:H68	H4	=IFERROR(IF(OR(DAY(B5)=11,DAY(B5)=21,DAY(B5)=1),SUM(G$3:G4)-SUM(H$2:H3),""),"")

 

[vishu]

@vishu Does this help?

Testing Formula Update.xlsm
	A	B	C	D	E	F	G	H
1			Item	Qty	Rate	Amt
2		1-Apr-24	Milk	15.5	50	775
3			Curd	3	75	225	1000
4		2-Apr-24	Milk	8	50	400
5			Paneer	3	300	900
6			Curd	3	75	225
7			Khava	1	300	300	1825
8		3-Apr-24	Milk	12	50	600
9			Paneer	3	300	900
10			Amul Butter	1	170	170	1670
11		4-Apr-24	Milk	6	50	300
12			Paneer	3	300	900
13			Curd	5	75	375
14			Khava	1	300	300	1875
15		5-Apr-24	Milk	10	50	500	500
16		6-Apr-24	Milk	12	50	600
17			Milk	15.5	50	775
18			Curd	3	75	225	1600
19		7-Apr-24	Milk	8	50	400
20			Paneer	3	300	900	1300
21		8-Apr-24	Curd	3	75	225
22			Khava	1	300	300
23			Milk	12	50	600	1125
24		9-Apr-24	Paneer	3	300	900
25			Amul Butter	1	170	170
26			Milk	6	50	300
27			Paneer	3	300	900	2270
28		10-Apr-24	Curd	5	75	375
29			Khava	1	300	300
30			Milk	10	50	500
31			Milk	12	50	600	1775	14940
32		11-Apr-24	Milk	12	50	600
33			Milk	15.5	50	775
34			Curd	3	75	225
35			Milk	8	50	400	2000
36		12-Apr-24	Paneer	3	300	900
37			Curd	3	75	225
38			Khava	1	300	300
39			Milk	12	50	600
40			Paneer	3	300	900	2925
41		13-Apr-24	Amul Butter	1	170	170
42			Milk	6	50	300
43			Paneer	3	300	900
44			Curd	5	75	375
45			Milk	12	50	600	2345
46		14-Apr-24	Milk	15.5	50	775
47			Curd	3	75	225
48			Milk	8	50	400
49			Paneer	3	300	900
50			Curd	3	75	225
51			Khava	1	300	300	2825
52		15-Apr-24	Milk	12	50	600
53			Paneer	3	300	900
54			Milk	12	50	600	2100
55		16-Apr-24	Milk	12	50	600
56			Curd	3	75	225	825
57		17-Apr-24	Milk	8	50	400
58			Paneer	3	300	900	1300
59		18-Apr-24	Curd	3	75	225
60			Khava	1	300	300
61			Milk	12	50	600	1125
62		19-Apr-24	Paneer	3	300	900
63			Amul Butter	1	170	170
64			Milk	6	50	300	1370
65		20-Apr-24	Paneer	3	300	900
66			Curd	5	75	375
67			Milk	12	50	600	1875	18690
68		21-Apr-24	Milk	15.5	50	775
Sheet1

Cell Formulas
Range	Formula
H2:H3	H2	=IFERROR(IF(OR(DAY(B3)=11,DAY(B3)=21,DAY(B3)=1),SUM(G2:G$3)-SUM(H1:H$2),""),"")
H4:H68	H4	=IFERROR(IF(OR(DAY(B5)=11,DAY(B5)=21,DAY(B5)=1),SUM(G$3:G4)-SUM(H$2:H3),""),"")

Thank you thank you thank you very very much sir

 

[vishu]

@vishu Does this help?

Testing Formula Update.xlsm
	A	B	C	D	E	F	G	H
1			Item	Qty	Rate	Amt
2		1-Apr-24	Milk	15.5	50	775
3			Curd	3	75	225	1000
4		2-Apr-24	Milk	8	50	400
5			Paneer	3	300	900
6			Curd	3	75	225
7			Khava	1	300	300	1825
8		3-Apr-24	Milk	12	50	600
9			Paneer	3	300	900
10			Amul Butter	1	170	170	1670
11		4-Apr-24	Milk	6	50	300
12			Paneer	3	300	900
13			Curd	5	75	375
14			Khava	1	300	300	1875
15		5-Apr-24	Milk	10	50	500	500
16		6-Apr-24	Milk	12	50	600
17			Milk	15.5	50	775
18			Curd	3	75	225	1600
19		7-Apr-24	Milk	8	50	400
20			Paneer	3	300	900	1300
21		8-Apr-24	Curd	3	75	225
22			Khava	1	300	300
23			Milk	12	50	600	1125
24		9-Apr-24	Paneer	3	300	900
25			Amul Butter	1	170	170
26			Milk	6	50	300
27			Paneer	3	300	900	2270
28		10-Apr-24	Curd	5	75	375
29			Khava	1	300	300
30			Milk	10	50	500
31			Milk	12	50	600	1775	14940
32		11-Apr-24	Milk	12	50	600
33			Milk	15.5	50	775
34			Curd	3	75	225
35			Milk	8	50	400	2000
36		12-Apr-24	Paneer	3	300	900
37			Curd	3	75	225
38			Khava	1	300	300
39			Milk	12	50	600
40			Paneer	3	300	900	2925
41		13-Apr-24	Amul Butter	1	170	170
42			Milk	6	50	300
43			Paneer	3	300	900
44			Curd	5	75	375
45			Milk	12	50	600	2345
46		14-Apr-24	Milk	15.5	50	775
47			Curd	3	75	225
48			Milk	8	50	400
49			Paneer	3	300	900
50			Curd	3	75	225
51			Khava	1	300	300	2825
52		15-Apr-24	Milk	12	50	600
53			Paneer	3	300	900
54			Milk	12	50	600	2100
55		16-Apr-24	Milk	12	50	600
56			Curd	3	75	225	825
57		17-Apr-24	Milk	8	50	400
58			Paneer	3	300	900	1300
59		18-Apr-24	Curd	3	75	225
60			Khava	1	300	300
61			Milk	12	50	600	1125
62		19-Apr-24	Paneer	3	300	900
63			Amul Butter	1	170	170
64			Milk	6	50	300	1370
65		20-Apr-24	Paneer	3	300	900
66			Curd	5	75	375
67			Milk	12	50	600	1875	18690
68		21-Apr-24	Milk	15.5	50	775
Sheet1

Cell Formulas
Range	Formula
H2:H3	H2	=IFERROR(IF(OR(DAY(B3)=11,DAY(B3)=21,DAY(B3)=1),SUM(G2:G$3)-SUM(H1:H$2),""),"")
H4:H68	H4	=IFERROR(IF(OR(DAY(B5)=11,DAY(B5)=21,DAY(B5)=1),SUM(G$3:G4)-SUM(H$2:H3),""),"")

Sir if possible can get same values from H column to I2 (1 to 10) J2 (11 to 20) K2 (21 to 28/29(Feb),30/31(April/May)) please

 

[vishu]

Please any help have tried =INDEX(G:G,RANDBETWEEN(1,COUNTA(G:G)),1) but no success

 

[Snakehips]

If you are wanting April 1 -10 in I2, April 11 - 20 in J2, April 21 - EOM. in K2, can you clarify where are you wanting the results for May and so on?
In I3, J3, K3. or in I,J,K of the row of 1st of May etc?
Also, can you either tolerate having the relevant date in every row of B, rather than the one date and blanks below?
Or can you work with a 'helper' column having all rows dated?

IF this can be done, do you still need the current 10day total that are in H ?

 

[vishu]

If you are wanting April 1 -10 in I2, April 11 - 20 in J2, April 21 - EOM. in K2, can you clarify where are you wanting the results for May and so on?
In I3, J3, K3. or in I,J,K of the row of 1st of May etc?
Also, can you either tolerate having the relevant date in every row of B, rather than the one date and blanks below?
Or can you work with a 'helper' column having all rows dated?

IF this can be done, do you still need the current 10day total that are in H ?

1) If possible Date Column remain same as one date others are blank below
2) Yes H Column result as given is perfect and needed.
3) Helper Column - No problem sir
4) Yes sir every months 3 values needed in I2, J2 and K2

Thank you very much sir you helping me a lot

 

[Snakehips]

If I understand correctly, see if this helps.
A is a helper column with date per row. It could be another column if being A is a problem.

Testing Formula Update.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Helper Date		Item	Qty	Rate	Amt	Day Amt	10 day Amt	Month	1st - 10th	11th-20th	21st-EOM
2	45383	1-Apr-24	Milk	15.5	50	775			January/24	0	0	0
3	45383		Curd	3	75	225	1000		February/24	0	0	0
4	45384	2-Apr-24	Milk	8	50	400			March/24	0	0	0
5	45384		Paneer	3	300	900			April/24	14940	18690	5270
6	45384		Curd	3	75	225			May/24	5275	4570	5695
7	45384		Khava	1	300	300	1825		June/24	5000	5350	6345
8	45385	3-Apr-24	Milk	12	50	600			July/24	1550	0	0
9	45385		Paneer	3	300	900			August/24	0	0	0
10	45385		Amul Butter	1	170	170	1670		September/24	0	0	0
11	45386	4-Apr-24	Milk	6	50	300			October/24	0	0	0
12	45386		Paneer	3	300	900			November/24	0	0	0
13	45386		Curd	5	75	375			December/24	0	0	0
14	45386		Khava	1	300	300	1875		Year Total	26765	28610	17310
15	45387	5-Apr-24	Milk	10	50	500	500
16	45388	6-Apr-24	Milk	12	50	600
17	45388		Milk	15.5	50	775
18	45388		Curd	3	75	225	1600
19	45389	7-Apr-24	Milk	8	50	400
20	45389		Paneer	3	300	900	1300
21	45390	8-Apr-24	Curd	3	75	225
22	45390		Khava	1	300	300
23	45390		Milk	12	50	600	1125
24	45391	9-Apr-24	Paneer	3	300	900
25	45391		Amul Butter	1	170	170
26	45391		Milk	6	50	300
27	45391		Paneer	3	300	900	2270
28	45392	10-Apr-24	Curd	5	75	375
29	45392		Khava	1	300	300
30	45392		Milk	10	50	500
31	45392		Milk	12	50	600	1775	14940
32	45393	11-Apr-24	Milk	12	50	600
33	45393		Milk	15.5	50	775
34	45393		Curd	3	75	225
35	45393		Milk	8	50	400	2000
36	45394	12-Apr-24	Paneer	3	300	900
37	45394		Curd	3	75	225
38	45394		Khava	1	300	300
39	45394		Milk	12	50	600
40	45394		Paneer	3	300	900	2925
41	45395	13-Apr-24	Amul Butter	1	170	170
42	45395		Milk	6	50	300
43	45395		Paneer	3	300	900
44	45395		Curd	5	75	375
45	45395		Milk	12	50	600	2345
46	45396	14-Apr-24	Milk	15.5	50	775
47	45396		Curd	3	75	225
48	45396		Milk	8	50	400
49	45396		Paneer	3	300	900
50	45396		Curd	3	75	225
51	45396		Khava	1	300	300	2825
52	45397	15-Apr-24	Milk	12	50	600
53	45397		Paneer	3	300	900
54	45397		Milk	12	50	600	2100
55	45398	16-Apr-24	Milk	12	50	600
56	45398		Curd	3	75	225	825
57	45399	17-Apr-24	Milk	8	50	400
58	45399		Paneer	3	300	900	1300
59	45400	18-Apr-24	Curd	3	75	225
60	45400		Khava	1	300	300
61	45400		Milk	12	50	600	1125
62	45401	19-Apr-24	Paneer	3	300	900
63	45401		Amul Butter	1	170	170
64	45401		Milk	6	50	300	1370
65	45402	20-Apr-24	Paneer	3	300	900
66	45402		Curd	5	75	375
67	45402		Milk	12	50	600	1875	18690
68	45403	21-Apr-24	Milk	15.5	50	775	100
Sheet1

Cell Formulas
Range	Formula
J2:J13	J2	=SUMIF(A:A,"<="&I2+9,F:F)-SUM(J$1:L1)
K2:K13	K2	=SUMIF(A:A,"<="&I2+19,F:F)-J2-SUM(J$1:L1)
L2:L13	L2	=SUMIF(A:A,"<="&I2+DAY(EOMONTH(I2,0))-1,F:F)-J2-K2-SUM(J$1:L1)
J14:L14	J14	=SUM(J2:J10)
A2:A68	A2	=IF(B2>0,B2,A1)
H2:H3	H2	=IFERROR(IF(OR(DAY(B3)=11,DAY(B3)=21,DAY(B3)=1),SUM(G2:G$3)-SUM(H1:H$2),""),"")
H4:H68	H4	=IFERROR(IF(OR(DAY(B5)=11,DAY(B5)=21,DAY(B5)=1),SUM(G$3:G4)-SUM(H$2:H3),""),"")

 

[vishu]

If I understand correctly, see if this helps.
A is a helper column with date per row. It could be another column if being A is a problem.

Testing Formula Update.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Helper Date		Item	Qty	Rate	Amt	Day Amt	10 day Amt	Month	1st - 10th	11th-20th	21st-EOM
2	45383	1-Apr-24	Milk	15.5	50	775			January/24	0	0	0
3	45383		Curd	3	75	225	1000		February/24	0	0	0
4	45384	2-Apr-24	Milk	8	50	400			March/24	0	0	0
5	45384		Paneer	3	300	900			April/24	14940	18690	5270
6	45384		Curd	3	75	225			May/24	5275	4570	5695
7	45384		Khava	1	300	300	1825		June/24	5000	5350	6345
8	45385	3-Apr-24	Milk	12	50	600			July/24	1550	0	0
9	45385		Paneer	3	300	900			August/24	0	0	0
10	45385		Amul Butter	1	170	170	1670		September/24	0	0	0
11	45386	4-Apr-24	Milk	6	50	300			October/24	0	0	0
12	45386		Paneer	3	300	900			November/24	0	0	0
13	45386		Curd	5	75	375			December/24	0	0	0
14	45386		Khava	1	300	300	1875		Year Total	26765	28610	17310
15	45387	5-Apr-24	Milk	10	50	500	500
16	45388	6-Apr-24	Milk	12	50	600
17	45388		Milk	15.5	50	775
18	45388		Curd	3	75	225	1600
19	45389	7-Apr-24	Milk	8	50	400
20	45389		Paneer	3	300	900	1300
21	45390	8-Apr-24	Curd	3	75	225
22	45390		Khava	1	300	300
23	45390		Milk	12	50	600	1125
24	45391	9-Apr-24	Paneer	3	300	900
25	45391		Amul Butter	1	170	170
26	45391		Milk	6	50	300
27	45391		Paneer	3	300	900	2270
28	45392	10-Apr-24	Curd	5	75	375
29	45392		Khava	1	300	300
30	45392		Milk	10	50	500
31	45392		Milk	12	50	600	1775	14940
32	45393	11-Apr-24	Milk	12	50	600
33	45393		Milk	15.5	50	775
34	45393		Curd	3	75	225
35	45393		Milk	8	50	400	2000
36	45394	12-Apr-24	Paneer	3	300	900
37	45394		Curd	3	75	225
38	45394		Khava	1	300	300
39	45394		Milk	12	50	600
40	45394		Paneer	3	300	900	2925
41	45395	13-Apr-24	Amul Butter	1	170	170
42	45395		Milk	6	50	300
43	45395		Paneer	3	300	900
44	45395		Curd	5	75	375
45	45395		Milk	12	50	600	2345
46	45396	14-Apr-24	Milk	15.5	50	775
47	45396		Curd	3	75	225
48	45396		Milk	8	50	400
49	45396		Paneer	3	300	900
50	45396		Curd	3	75	225
51	45396		Khava	1	300	300	2825
52	45397	15-Apr-24	Milk	12	50	600
53	45397		Paneer	3	300	900
54	45397		Milk	12	50	600	2100
55	45398	16-Apr-24	Milk	12	50	600
56	45398		Curd	3	75	225	825
57	45399	17-Apr-24	Milk	8	50	400
58	45399		Paneer	3	300	900	1300
59	45400	18-Apr-24	Curd	3	75	225
60	45400		Khava	1	300	300
61	45400		Milk	12	50	600	1125
62	45401	19-Apr-24	Paneer	3	300	900
63	45401		Amul Butter	1	170	170
64	45401		Milk	6	50	300	1370
65	45402	20-Apr-24	Paneer	3	300	900
66	45402		Curd	5	75	375
67	45402		Milk	12	50	600	1875	18690
68	45403	21-Apr-24	Milk	15.5	50	775	100
Sheet1

Cell Formulas
Range	Formula
J2:J13	J2	=SUMIF(A:A,"<="&I2+9,F:F)-SUM(J$1:L1)
K2:K13	K2	=SUMIF(A:A,"<="&I2+19,F:F)-J2-SUM(J$1:L1)
L2:L13	L2	=SUMIF(A:A,"<="&I2+DAY(EOMONTH(I2,0))-1,F:F)-J2-K2-SUM(J$1:L1)
J14:L14	J14	=SUM(J2:J10)
A2:A68	A2	=IF(B2>0,B2,A1)
H2:H3	H2	=IFERROR(IF(OR(DAY(B3)=11,DAY(B3)=21,DAY(B3)=1),SUM(G2:G$3)-SUM(H1:H$2),""),"")
H4:H68	H4	=IFERROR(IF(OR(DAY(B5)=11,DAY(B5)=21,DAY(B5)=1),SUM(G$3:G4)-SUM(H$2:H3),""),"")

Love you sir, tooooo goooooddddd. Thank you very much