function in excel to get subtotals of each month

[RAJESH1960]

Hello guys
I have this statement of one year. I want to get the subtotals at the end of each month. For now, I am inserting a column and entering manually each month name next to the dates from 1 to 30 or 31 whatever the case. Then I am using sub total. I want to know whether there Is a function in the menu where I can get the subtotal directly without inserting the column and entering each month manually.?

subtotal at each month.xlsx
	A	B	C	D
1	Line	Date	Debit	Credit
2	1	02-04-2021		27285
3	2	03-04-2021		19966
4	3	05-04-2021	38000	80487
5	4	06-04-2021		17532
6	5	07-04-2021		38001
7	6	08-04-2021	16652	9327
8	7	09-04-2021	20000	37125
9	8	10-04-2021		22673
10	9	11-04-2021	31621.82	44660
11	10	12-04-2021	91777	58540
12	11	13-04-2021	149601	159063
13	12	14-04-2021	66473	77007
14	13	15-04-2021	56826	50555
15	14	16-04-2021	399970	183196
16	15	17-04-2021	246167	110988
17	16	19-04-2021		228359
18	17	20-04-2021		435
19	18	21-04-2021	36640	129300
20	19	22-04-2021	97090	113085
21	20	23-04-2021	11452	6262
22	21	26-04-2021	50000	25000
23	22	27-04-2021	5468	30000
24	23	28-04-2021	30000
25	24	29-04-2021		6695
26	25	30-04-2021	176483	27393
27	26	05-05-2021		16757
28	27	21-05-2021		6630
29	28	31-05-2021	148059	22407
30	29	16-06-2021		4600
31	30	22-06-2021		27620
32	31	23-06-2021		11746
33	32	24-06-2021		16875
34	33	28-06-2021		20000
35	34	29-06-2021		6200
36	35	30-06-2021	144150	24000
37	36	01-07-2021		9924
38	37	02-07-2021		2854
39	38	05-07-2021		28706
40	39	06-07-2021		15414
41	40	07-07-2021		8000
42	41	08-07-2021		12802
43	42	09-07-2021		14650
44	43	10-07-2021		3896
45	44	12-07-2021		30020
46	45	13-07-2021		10500
47	46	14-07-2021		17368
48	47	15-07-2021		5730
49	48	16-07-2021		16369
50	49	17-07-2021		13217
51	50	19-07-2021		20556
52	51	20-07-2021		10610
53	52	22-07-2021		1723
54	53	23-07-2021		16540
55	54	26-07-2021	3100	130767
56	55	27-07-2021	73563.56	20249
57	56	28-07-2021	193552	220480
58	57	29-07-2021	104928	79156
59	58	30-07-2021	308496	820017
60	59	31-07-2021	717902	37284
61	60	02-08-2021	6100	24244
62	61	03-08-2021	266795	574620
63	62	04-08-2021	637322	514321
64	63	05-08-2021	452113	613855
65	64	06-08-2021	228042	333671
66	65	07-08-2021	326887	55268
67	66	08-08-2021	42998	63999
68	67	09-08-2021	514401	464732
69	68	10-08-2021	163770	182630
70	69	11-08-2021	119956	180423
71	70	12-08-2021	161474	120269
72	71	13-08-2021	207328.8	181354
73	72	14-08-2021	44000	31234
74	73	15-08-2021	29250	45169
75	74	16-08-2021	142106	144115
76	75	17-08-2021	150910	132870
77	76	18-08-2021	21000	3377
78	77	19-08-2021	57917	86152
79	78	20-08-2021	172013	162123
80	79	21-08-2021	93014	92541
81	80	22-08-2021	8000	11475
82	81	23-08-2021	149300	145812
83	82	24-08-2021	83492	82610
84	83	25-08-2021	392135	414034
85	84	26-08-2021	77850	55788
86	85	27-08-2021	127318	129317
87	86	28-08-2021	47193	76495
88	87	29-08-2021	45000	18111
89	88	30-08-2021	115647	124898
90	89	31-08-2021	280198	123548
91	90	01-09-2021		13302
92	91	02-09-2021		44540
93	92	03-09-2021		30212
94	93	04-09-2021		1794
95	94	06-09-2021		11588
96	95	07-09-2021		33975
97	96	08-09-2021	11527	40983
98	97	09-09-2021	183898	168284
99	98	10-09-2021	50660	68173
100	99	11-09-2021	85000	64447
101	100	12-09-2021	20250	33323
102	101	13-09-2021	100636	64711
103	102	14-09-2021	84211	166154
104	103	15-09-2021	155661	117543
105	104	16-09-2021	135773	112113
106	105	17-09-2021	55667	50959
107	106	18-09-2021	108386.8	115388
108	107	19-09-2021	41333	39345
109	108	20-09-2021	91048	102222
110	109	21-09-2021	62442	51795
111	110	22-09-2021	35000	27738
112	111	23-09-2021	25625	23884
113	112	24-09-2021	131465	141853
114	113	25-09-2021	19125	25322
115	114	26-09-2021	57600	48720
116	115	27-09-2021	60822	60949
117	116	28-09-2021	179177	184580
118	117	29-09-2021	91157	91466
119	118	30-09-2021	180477	32514
120	119	01-10-2021		5597
121	120	02-10-2021		34828
122	121	04-10-2021		19594
123	122	05-10-2021	18506	101070
124	123	06-10-2021	21000	4362
125	124	07-10-2021	46100	63509
126	125	08-10-2021	50263	18893
127	126	09-10-2021	1180	7895
128	127	10-10-2021		15766
129	128	11-10-2021	90156	122072
130	129	12-10-2021	81901	35786
131	130	13-10-2021	41350	75611
132	131	14-10-2021	35480	14381
133	132	15-10-2021	236
134	133	16-10-2021	60362	43400
135	134	17-10-2021		20000
136	135	18-10-2021	29250	10977
137	136	19-10-2021	236	4000
138	137	21-10-2021	21500	44897
139	138	22-10-2021	37472	18348
140	139	24-10-2021		2000
141	140	25-10-2021	44424	122004
142	141	26-10-2021	104693	43661
143	142	27-10-2021	123326	124930
144	143	28-10-2021	20000
145	144	29-10-2021	2522
146	145	30-10-2021	11250	16598
147	146	31-10-2021	155922
148	147	01-11-2021		3460
149	148	02-11-2021		38408
150	149	04-11-2021		55273
151	150	05-11-2021		3520
152	151	06-11-2021		21709
153	152	08-11-2021		47625
154	153	09-11-2021	30708	10000
155	154	10-11-2021	22050	9500
156	155	11-11-2021	484584	569675
157	156	12-11-2021	81036
158	157	14-11-2021	205399.8	239742
159	158	15-11-2021	168600	169727
160	159	16-11-2021	108928	122170
161	160	17-11-2021	61145	32050
162	161	18-11-2021	36000	72642
163	162	19-11-2021	88143	60340
164	163	20-11-2021	105728	266585
165	164	21-11-2021	54969
166	165	22-11-2021	88859	13540
167	166	23-11-2021	211658	181405
168	167	24-11-2021	22793	3458
169	168	25-11-2021	590	12310
170	169	26-11-2021	27277	14259
171	170	28-11-2021		4375
172	171	29-11-2021	13589	10699
173	172	30-11-2021	160155	8857
174	173	02-12-2021		33736
175	174	03-12-2021		4983
176	175	04-12-2021		1145
177	176	06-12-2021		11867
178	177	07-12-2021		55327
179	178	08-12-2021		29981
180	179	09-12-2021		9293
181	180	10-12-2021	1439.6	2445
182	181	11-12-2021	9013	31962
183	182	12-12-2021	52689.54	56440
184	183	13-12-2021	17236	13645
185	184	14-12-2021	89006	88614
186	185	15-12-2021	29555	19961
187	186	16-12-2021	34000	43261
188	187	17-12-2021	35759	37671
189	188	18-12-2021	126036	224070
190	189	19-12-2021	126500	27000
191	190	20-12-2021	137000	138449
192	191	21-12-2021	34288	28406
193	192	22-12-2021	30236	9775
194	193	23-12-2021	44995	69710
195	194	24-12-2021	15209	5876
196	195	25-12-2021		4550
197	196	27-12-2021	510209	515406
198	197	28-12-2021	173700	173182
199	198	29-12-2021	29486
200	199	30-12-2021	3616	33999
201	200	31-12-2021	303275	128922
202	201	01-01-2022		1
203	202	03-01-2022		19678
204	203	04-01-2022		2548
205	204	06-01-2022		8639
206	205	07-01-2022	34045	34045
207	206	11-01-2022		15342
208	207	12-01-2022		19840
209	208	13-01-2022		398
210	209	14-01-2022		60301
211	210	17-01-2022	159367	205723
212	211	18-01-2022	158416.6	159080
213	212	19-01-2022	55069	22962
214	213	20-01-2022	1100	32664
215	214	21-01-2022	45327	32497
216	215	25-01-2022	222522	256422
217	216	26-01-2022	40145	20000
218	217	27-01-2022	28169	3498
219	218	28-01-2022	124046	153387
220	219	29-01-2022	316075	1025383
221	220	30-01-2022	585553	2727
222	221	31-01-2022	562755	200000
Sheet1

If not possible a code will be helpful.

 

[Dave Patton]

I have never used Excel 2019,

Look through the menus Such as Data

The feature is in very early versions of Excel.

There are also many videos on using the feature with Excel 2019.

 

[RAJESH1960]

I found one option in pivot table and got the subtotals at the end of each month. But they will be helpful only for 1 year only. If the data is for 2 years it will not be helpful. PeterSS's code works for multiple years too.
Anyway thanks for helping Dave. ?

subtotal at each month.xlsm
	A	B	C
1
2
3	Row Labels	Sum of Debit	Sum of Credit
4	Jan	2332589.6	2275135
5	Apr	1524220.82	1502934
6	May	148059	45794
7	Jun	144150	111041
8	Jul	1401541.56	1546832
9	Aug	5163529.82	5189055
10	Sep	1966940.82	1967877
11	Oct	997129	970179
12	Nov	1972211.84	1971329
13	Dec	1803248.14	1799676
14	Grand Total	17453620.6	17379852
15
Sheet2

 

[Dave Patton]

The Subtotal feature works with multiple years. Please read the posts and look at the examples.
If you do not want the net change for each month, delete that column.
N.B. Do not enter the subtotal formulas, the feature Subtotal enters those formulas.

T202203.xlsm
	A	B	C	D	E
1	Date	Debit	Credit	Net	Text_yyyymmmdd
27		1,524,220.82	1,502,934.00	21,286.82	2021Apr30
31		148,059.00	45,794.00	102,265.00	2021May31
39		144,150.00	111,041.00	33,109.00	2021Jun30
64		1,401,541.56	1,546,832.00	-145,290.44	2021Jul31
95		5,163,529.82	5,189,055.00	-25,525.18	2021Aug31
125		1,966,940.82	1,967,877.00	-936.18	2021Sep30
154		997,129.00	970,179.00	26,950.00	2021Oct31
181		1,972,211.84	1,971,329.00	882.84	2021Nov30
210		1,803,248.14	1,799,676.00	3,572.14	2021Dec31
232		2,332,589.60	2,275,135.00	57,454.60	2022Jan31
233		17,453,620.60	17,379,852.00	73,768.60	Grand Total
234
6a

Cell Formulas
Range	Formula
B27:D27	B27	=SUBTOTAL(9,B2:B26)
B31:D31	B31	=SUBTOTAL(9,B28:B30)
B39:D39	B39	=SUBTOTAL(9,B32:B38)
B64:D64	B64	=SUBTOTAL(9,B40:B63)
B95:D95	B95	=SUBTOTAL(9,B65:B94)
B125:D125	B125	=SUBTOTAL(9,B96:B124)
B154:D154,B210:D210	B154	=SUBTOTAL(9,B126:B153)
B181:D181	B181	=SUBTOTAL(9,B155:B180)
B232:D232	B232	=SUBTOTAL(9,B211:B231)
B233:D233	B233	=SUBTOTAL(9,B2:B231)

 

[RAJESH1960]

I checked the whole menu bar but I didn't find the functions you are talking about in my version of excel. Can you please record a video of how you are doing it and share with me the link to the video.?

 

[Dave Patton]

Previous
I have never used Excel 2019,

Look through the menus Such as Data

The feature is in very early versions of Excel.

There are also many videos on using the feature with Excel 2019.

Try searching on line for "Excel version 2019 subtotal feature".

If I recorded a video, it would not help you since I only have access to 2010 and 365.

 

[RAJESH1960]

Excel version 2019 subtotal feature".

Okies. Will check.

 

[Dave Patton]

I do not know if this will help you but

"
How to find the subtotal button in Microsoft Excel 2007/2010/2013/2016/2019 ribbon?
Get Subtotal Button in Ribbon if you do not have Classic Menu for Office. If you did not installed Classic Menu for Office on your computer, this tip will help you find out the Subtotal Button in Microsoft Excel 2007/2010/2013/2016/2019 Ribbon: Click the Data tab; Go to the Outline group; Then you will view the Subtotal button there.

 

[RAJESH1960]

I have the subtotal button in my excel and I have been using it regularly whenever required. But it doesn't have the options to check the sum for each month. I can select the first option, at each change in, Date, in the list of 4 options only. Line, Date, Debit and Credit. There ain't any option to select monthly or yearly.

 

[Dave Patton]

Try reading post #3.

 

[RAJESH1960]

Try reading post #3.

Now, again after reading your post #3, I finally understood what you were trying to explain. Insert column and apply formula=TEXT(B2,"mmm-yyyy") to get month and year and drag down. Then apply sub total ....?
I was able to get the correct result. Thanks man.