function in excel to get subtotals of each month

RAJESH1960

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
Office Version
  1. 2019
Platform
  1. Windows
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
ABCD
1LineDateDebitCredit
2102-04-202127285
3203-04-202119966
4305-04-20213800080487
5406-04-202117532
6507-04-202138001
7608-04-2021166529327
8709-04-20212000037125
9810-04-202122673
10911-04-202131621.8244660
111012-04-20219177758540
121113-04-2021149601159063
131214-04-20216647377007
141315-04-20215682650555
151416-04-2021399970183196
161517-04-2021246167110988
171619-04-2021228359
181720-04-2021435
191821-04-202136640129300
201922-04-202197090113085
212023-04-2021114526262
222126-04-20215000025000
232227-04-2021546830000
242328-04-202130000
252429-04-20216695
262530-04-202117648327393
272605-05-202116757
282721-05-20216630
292831-05-202114805922407
302916-06-20214600
313022-06-202127620
323123-06-202111746
333224-06-202116875
343328-06-202120000
353429-06-20216200
363530-06-202114415024000
373601-07-20219924
383702-07-20212854
393805-07-202128706
403906-07-202115414
414007-07-20218000
424108-07-202112802
434209-07-202114650
444310-07-20213896
454412-07-202130020
464513-07-202110500
474614-07-202117368
484715-07-20215730
494816-07-202116369
504917-07-202113217
515019-07-202120556
525120-07-202110610
535222-07-20211723
545323-07-202116540
555426-07-20213100130767
565527-07-202173563.5620249
575628-07-2021193552220480
585729-07-202110492879156
595830-07-2021308496820017
605931-07-202171790237284
616002-08-2021610024244
626103-08-2021266795574620
636204-08-2021637322514321
646305-08-2021452113613855
656406-08-2021228042333671
666507-08-202132688755268
676608-08-20214299863999
686709-08-2021514401464732
696810-08-2021163770182630
706911-08-2021119956180423
717012-08-2021161474120269
727113-08-2021207328.8181354
737214-08-20214400031234
747315-08-20212925045169
757416-08-2021142106144115
767517-08-2021150910132870
777618-08-2021210003377
787719-08-20215791786152
797820-08-2021172013162123
807921-08-20219301492541
818022-08-2021800011475
828123-08-2021149300145812
838224-08-20218349282610
848325-08-2021392135414034
858426-08-20217785055788
868527-08-2021127318129317
878628-08-20214719376495
888729-08-20214500018111
898830-08-2021115647124898
908931-08-2021280198123548
919001-09-202113302
929102-09-202144540
939203-09-202130212
949304-09-20211794
959406-09-202111588
969507-09-202133975
979608-09-20211152740983
989709-09-2021183898168284
999810-09-20215066068173
1009911-09-20218500064447
10110012-09-20212025033323
10210113-09-202110063664711
10310214-09-202184211166154
10410315-09-2021155661117543
10510416-09-2021135773112113
10610517-09-20215566750959
10710618-09-2021108386.8115388
10810719-09-20214133339345
10910820-09-202191048102222
11010921-09-20216244251795
11111022-09-20213500027738
11211123-09-20212562523884
11311224-09-2021131465141853
11411325-09-20211912525322
11511426-09-20215760048720
11611527-09-20216082260949
11711628-09-2021179177184580
11811729-09-20219115791466
11911830-09-202118047732514
12011901-10-20215597
12112002-10-202134828
12212104-10-202119594
12312205-10-202118506101070
12412306-10-2021210004362
12512407-10-20214610063509
12612508-10-20215026318893
12712609-10-202111807895
12812710-10-202115766
12912811-10-202190156122072
13012912-10-20218190135786
13113013-10-20214135075611
13213114-10-20213548014381
13313215-10-2021236
13413316-10-20216036243400
13513417-10-202120000
13613518-10-20212925010977
13713619-10-20212364000
13813721-10-20212150044897
13913822-10-20213747218348
14013924-10-20212000
14114025-10-202144424122004
14214126-10-202110469343661
14314227-10-2021123326124930
14414328-10-202120000
14514429-10-20212522
14614530-10-20211125016598
14714631-10-2021155922
14814701-11-20213460
14914802-11-202138408
15014904-11-202155273
15115005-11-20213520
15215106-11-202121709
15315208-11-202147625
15415309-11-20213070810000
15515410-11-2021220509500
15615511-11-2021484584569675
15715612-11-202181036
15815714-11-2021205399.8239742
15915815-11-2021168600169727
16015916-11-2021108928122170
16116017-11-20216114532050
16216118-11-20213600072642
16316219-11-20218814360340
16416320-11-2021105728266585
16516421-11-202154969
16616522-11-20218885913540
16716623-11-2021211658181405
16816724-11-2021227933458
16916825-11-202159012310
17016926-11-20212727714259
17117028-11-20214375
17217129-11-20211358910699
17317230-11-20211601558857
17417302-12-202133736
17517403-12-20214983
17617504-12-20211145
17717606-12-202111867
17817707-12-202155327
17917808-12-202129981
18017909-12-20219293
18118010-12-20211439.62445
18218111-12-2021901331962
18318212-12-202152689.5456440
18418313-12-20211723613645
18518414-12-20218900688614
18618515-12-20212955519961
18718616-12-20213400043261
18818717-12-20213575937671
18918818-12-2021126036224070
19018919-12-202112650027000
19119020-12-2021137000138449
19219121-12-20213428828406
19319222-12-2021302369775
19419323-12-20214499569710
19519424-12-2021152095876
19619525-12-20214550
19719627-12-2021510209515406
19819728-12-2021173700173182
19919829-12-202129486
20019930-12-2021361633999
20120031-12-2021303275128922
20220101-01-20221
20320203-01-202219678
20420304-01-20222548
20520406-01-20228639
20620507-01-20223404534045
20720611-01-202215342
20820712-01-202219840
20920813-01-2022398
21020914-01-202260301
21121017-01-2022159367205723
21221118-01-2022158416.6159080
21321219-01-20225506922962
21421320-01-2022110032664
21521421-01-20224532732497
21621525-01-2022222522256422
21721626-01-20224014520000
21821727-01-2022281693498
21921828-01-2022124046153387
22021929-01-20223160751025383
22122030-01-20225855532727
22222131-01-2022562755200000
Sheet1

If not possible a code will be helpful.
 
Last edited:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
See if this helps.

VBA Code: 
Sub ST()
  Columns("C").Insert
  Range("C1").Value = "Month"
  With Range("A1").CurrentRegion
    With .Columns(3).SpecialCells(xlBlanks)
      .NumberFormat = "@"
      .Value = Evaluate("text(" & .Offset(, -1).Address & ",""mmm-yyyy"")")
    End With
    .Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(4, 5), Replace:=True
  End With
End Sub
 
Upvote 0
Solution
An alternative that you can consider is adding a criteria such as yyyymm in a column and combine the
Debit and Credit amount in a column.
Then use Subtotal from the Data menu, This feature will add the formulas.
You would then have 3 choices 1 view grand total, 2 view subtotals and grand total and 3 view all entries and subtotal.

I will show part of the view 3 all details and the view 2 Subtotals.

T202203.xlsm
ABCDEF
1LineDateDebitCreditNetyyyymm
212-Apr-2127,285.00-27,285.00202104
323-Apr-2119,966.00-19,966.00202104
435-Apr-2138,000.0080,487.00-42,487.00202104
546-Apr-2117,532.00-17,532.00202104
657-Apr-2138,001.00-38,001.00202104
768-Apr-2116,652.009,327.007,325.00202104
879-Apr-2120,000.0037,125.00-17,125.00202104
9810-Apr-2122,673.00-22,673.00202104
10911-Apr-2131,621.8244,660.00-13,038.18202104
111012-Apr-2191,777.0058,540.0033,237.00202104
121113-Apr-21149,601.00159,063.00-9,462.00202104
131214-Apr-2166,473.0077,007.00-10,534.00202104
141315-Apr-2156,826.0050,555.006,271.00202104
151416-Apr-21399,970.00183,196.00216,774.00202104
161517-Apr-21246,167.00110,988.00135,179.00202104
171619-Apr-21228,359.00-228,359.00202104
181720-Apr-21435.00-435.00202104
191821-Apr-2136,640.00129,300.00-92,660.00202104
201922-Apr-2197,090.00113,085.00-15,995.00202104
212023-Apr-2111,452.006,262.005,190.00202104
222126-Apr-2150,000.0025,000.0025,000.00202104
232227-Apr-215,468.0030,000.00-24,532.00202104
242328-Apr-2130,000.0030,000.00202104
252429-Apr-216,695.00-6,695.00202104
262530-Apr-21176,483.0027,393.00149,090.00202104
2721,286.82202104 Total
28265-May-2116,757.00-16,757.00202105
6a
Cell Formulas
RangeFormula
F2:F26,F28F2=TEXT(B2,"yyyymm")
E2:E26,E28E2=C2-D2
E27E27=SUBTOTAL(9,E2:E26)


T202203.xlsm
ABCDEF
1LineDateDebitCreditNetyyyymm
2721,286.82202104 Total
31102,265.00202105 Total
3933,109.00202106 Total
64-145,290.44202107 Total
95-25,525.18202108 Total
125-936.18202109 Total
15426,950.00202110 Total
181882.84202111 Total
2103,572.14202112 Total
23257,454.60202201 Total
23373,768.60Grand Total
234
235
236
6a
Cell Formulas
RangeFormula
E27E27=SUBTOTAL(9,E2:E26)
E31E31=SUBTOTAL(9,E28:E30)
E39E39=SUBTOTAL(9,E32:E38)
E64E64=SUBTOTAL(9,E40:E63)
E95E95=SUBTOTAL(9,E65:E94)
E125E125=SUBTOTAL(9,E96:E124)
E154,E210E154=SUBTOTAL(9,E126:E153)
E181E181=SUBTOTAL(9,E155:E180)
E232E232=SUBTOTAL(9,E211:E231)
E233E233=SUBTOTAL(9,E2:E231)
 
Upvote 0
Peter_SSs said:
See if this helps.

VBA Code: 
Sub ST()
  Columns("C").Insert
  Range("C1").Value = "Month"
  With Range("A1").CurrentRegion
    With .Columns(3).SpecialCells(xlBlanks)
      .NumberFormat = "@"
      .Value = Evaluate("text(" & .Offset(, -1).Address & ",""mmm-yyyy"")")
    End With
    .Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(4, 5), Replace:=True
  End With
End Sub
Click to expand...
PURRRRFECT....?? Thanks PeterSSs.
 
Upvote 0
Dave Patton said:
An alternative that you can consider is adding a criteria such as yyyymm in a column and combine the
Debit and Credit amount in a column.
Then use Subtotal from the Data menu, This feature will add the formulas.
You would then have 3 choices 1 view grand total, 2 view subtotals and grand total and 3 view all entries and subtotal.

I will show part of the view 3 all details and the view 2 Subtotals.

T202203.xlsm
ABCDEF
1LineDateDebitCreditNetyyyymm
212-Apr-2127,285.00-27,285.00202104
323-Apr-2119,966.00-19,966.00202104
435-Apr-2138,000.0080,487.00-42,487.00202104
546-Apr-2117,532.00-17,532.00202104
657-Apr-2138,001.00-38,001.00202104
768-Apr-2116,652.009,327.007,325.00202104
879-Apr-2120,000.0037,125.00-17,125.00202104
9810-Apr-2122,673.00-22,673.00202104
10911-Apr-2131,621.8244,660.00-13,038.18202104
111012-Apr-2191,777.0058,540.0033,237.00202104
121113-Apr-21149,601.00159,063.00-9,462.00202104
131214-Apr-2166,473.0077,007.00-10,534.00202104
141315-Apr-2156,826.0050,555.006,271.00202104
151416-Apr-21399,970.00183,196.00216,774.00202104
161517-Apr-21246,167.00110,988.00135,179.00202104
171619-Apr-21228,359.00-228,359.00202104
181720-Apr-21435.00-435.00202104
191821-Apr-2136,640.00129,300.00-92,660.00202104
201922-Apr-2197,090.00113,085.00-15,995.00202104
212023-Apr-2111,452.006,262.005,190.00202104
222126-Apr-2150,000.0025,000.0025,000.00202104
232227-Apr-215,468.0030,000.00-24,532.00202104
242328-Apr-2130,000.0030,000.00202104
252429-Apr-216,695.00-6,695.00202104
262530-Apr-21176,483.0027,393.00149,090.00202104
2721,286.82202104 Total
28265-May-2116,757.00-16,757.00202105
6a
Cell Formulas
RangeFormula
F2:F26,F28F2=TEXT(B2,"yyyymm")
E2:E26,E28E2=C2-D2
E27E27=SUBTOTAL(9,E2:E26)


T202203.xlsm
ABCDEF
1LineDateDebitCreditNetyyyymm
2721,286.82202104 Total
31102,265.00202105 Total
3933,109.00202106 Total
64-145,290.44202107 Total
95-25,525.18202108 Total
125-936.18202109 Total
15426,950.00202110 Total
181882.84202111 Total
2103,572.14202112 Total
23257,454.60202201 Total
23373,768.60Grand Total
234
235
236
6a
Cell Formulas
RangeFormula
E27E27=SUBTOTAL(9,E2:E26)
E31E31=SUBTOTAL(9,E28:E30)
E39E39=SUBTOTAL(9,E32:E38)
E64E64=SUBTOTAL(9,E40:E63)
E95E95=SUBTOTAL(9,E65:E94)
E125E125=SUBTOTAL(9,E96:E124)
E154,E210E154=SUBTOTAL(9,E126:E153)
E181E181=SUBTOTAL(9,E155:E180)
E232E232=SUBTOTAL(9,E211:E231)
E233E233=SUBTOTAL(9,E2:E231)
Click to expand...
Thanks Dave. But I wanted the subtotals at the end of each month.
 
Upvote 0
"Thanks Dave. But I wanted the subtotals at the end of each month."

The Subtotal feature provides the total at the end of each month and more. It provides three views .
1 view. The Grand Total
2 view. The Total by Month and the Grand Total
3. view. The daily entries and the Total by Month and the Grand Total
4 It could also show the above information for the debit and Credit columns

Try the feature and ensure that you use "at each change in" the heading that I named Text_yyyymmmdd.

T202203.xlsm
ABCDE
1DateDebitCreditNetText_yyyymmmdd
2721,286.822021Apr30 Total
31102,265.002021May31 Total
3933,109.002021Jun30 Total
64-145,290.442021Jul31 Total
95-25,525.182021Aug31 Total
125-936.182021Sep30 Total
15426,950.002021Oct31 Total
181882.842021Nov30 Total
2103,572.142021Dec31 Total
23257,454.602022Jan31 Total
23373,768.60Grand Total
234
6a
Cell Formulas
RangeFormula
D27D27=SUBTOTAL(9,D2:D26)
D31D31=SUBTOTAL(9,D28:D30)
D39D39=SUBTOTAL(9,D32:D38)
D64D64=SUBTOTAL(9,D40:D63)
D95D95=SUBTOTAL(9,D65:D94)
D125D125=SUBTOTAL(9,D96:D124)
D154,D210D154=SUBTOTAL(9,D126:D153)
D181D181=SUBTOTAL(9,D155:D180)
D232D232=SUBTOTAL(9,D211:D231)
D233D233=SUBTOTAL(9,D2:D231)
 
Last edited:
Upvote 0
Sorry, Dave. I am unable to understand what you meant. I am not able to save your excel sheet as well. Can you please elaborate in simpler way.? How exactly did you arrive at the subtotals in column D and E ?
Dave Patton said:
Try the feature and ensure that you use "at each change in" the heading that I named Text_yyyymmmdd.
Click to expand...
 
Upvote 0
I have never used Excel 2019,

Look through the menus Such as Data

The feature is in very early versions of Excel,
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Help Please
2 3
Replies
21
Views
405
Asbestos_Jen
Asbestos_Jen
V
  • Question Question
US/European Date Format Updates Not Working on Macro Run
Replies
0
Views
63
vancher19
V
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
247
dshtesy
D
S
  • Solved
Count of active employees between dates.
Replies
3
Views
631
Fluff
Fluff
E
  • Question Question
Pulling together data from multiple MS Forms spreadsheets and displaying into a single league table
Replies
6
Views
286
excelnewbie999
E

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top