Sum values in a column based on date

LBDaye · Apr 4th 2019

Hello,
Could you please help creating a macro that sum up amount on Column C, then list the result on Column D, if the date on Column B is same?
Below is part of a very long worksheet that I manually sum up and highlighted based on same date on Column B. Thank you for your HELP!
[TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 278"]

[tr]

[TD="class: xl63, width: 64"]Alpha[/TD]
[TD="class: xl64, width: 179"]19-02-25[/TD]
[TD="class: xl65, width: 64"] 12.99[/TD]
[TD="class: xl66, width: 64"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]Alpha[/TD]
[TD="class: xl64"]19-02-25[/TD]
[TD="class: xl65"] 7.99[/TD]
[TD="class: xl66"] 20.98[/TD]

[/tr]

[tr]

[td]

Alpha

[/td]

[TD="class: xl67"]19-03-03[/TD]
[TD="class: xl68"] 15.99[/TD]
[TD="class: xl69"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]Alpha[/TD]
[TD="class: xl64"]19-03-09[/TD]
[TD="class: xl65"] 3.44[/TD]
[TD="class: xl66"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]Alpha[/TD]
[TD="class: xl64"]19-03-09[/TD]
[TD="class: xl65"] 4.99[/TD]
[TD="class: xl66"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]Alpha[/TD]
[TD="class: xl64"]19-03-09[/TD]
[TD="class: xl65"] 14.99[/TD]
[TD="class: xl66"] 23.42[/TD]

[/tr]

[tr]

[td]

Alpha

[/td]

[TD="class: xl67"]19-03-13[/TD]
[TD="class: xl68"] 7.99[/TD]
[TD="class: xl69"] [/TD]

[/tr]

[tr]

[td]

Alpha

[/td]

[TD="class: xl67"]19-03-14[/TD]
[TD="class: xl68"] 18.99[/TD]
[TD="class: xl69"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]Alpha[/TD]
[TD="class: xl64"]19-03-25[/TD]
[TD="class: xl65"] 22.00[/TD]
[TD="class: xl66"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]Alpha[/TD]
[TD="class: xl64"]19-03-25[/TD]
[TD="class: xl65"] 19.99[/TD]
[TD="class: xl66"] 41.99[/TD]

[/tr]

[tr]

[td][/td]

[TD="class: xl67"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl69"] [/TD]

[/tr]

[tr]

[td][/td]

[TD="class: xl67"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl69"] [/TD]

[/tr]

[tr]

[td][/td]

[TD="class: xl67"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl69"] [/TD]

[/tr]

[tr]

[td]

Michael

[/td]

[TD="class: xl67"]19-02-22[/TD]
[TD="class: xl68"] 94.38[/TD]
[TD="class: xl69"] [/TD]

[/tr]

[tr]

[td]

Michael

[/td]

[TD="class: xl67"]19-02-23[/TD]
[TD="class: xl68"] 10.00[/TD]
[TD="class: xl69"] [/TD]

[/tr]

[tr]

[TD="class: xl70"]Michael[/TD]
[TD="class: xl71"]19-02-24[/TD]
[TD="class: xl72"] 7.99[/TD]
[TD="class: xl72"] [/TD]

[/tr]

[tr]

[TD="class: xl70"]Michael[/TD]
[TD="class: xl71"]19-02-24[/TD]
[TD="class: xl72"] 9.99[/TD]
[TD="class: xl72"] [/TD]

[/tr]

[tr]

[TD="class: xl70"]Michael[/TD]
[TD="class: xl71"]19-02-24[/TD]
[TD="class: xl72"] 16.99[/TD]
[TD="class: xl72"] 34.97[/TD]

[/tr]

[tr]

[td]

Michael

[/td]

[TD="class: xl67"]19-02-25[/TD]
[TD="class: xl68"] 31.95[/TD]
[TD="class: xl69"] [/TD]

[/tr]

[tr]

[td]

Michael

[/td]

[TD="class: xl67"]19-02-27[/TD]
[TD="class: xl68"] 26.60[/TD]
[TD="class: xl69"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]Michael[/TD]
[TD="class: xl64"]19-02-28[/TD]
[TD="class: xl65"] 17.95[/TD]
[TD="class: xl65"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]Michael[/TD]
[TD="class: xl64"]19-02-28[/TD]
[TD="class: xl65"] 27.95[/TD]
[TD="class: xl65"] 45.90[/TD]

[/tr]

[tr]

[TD="class: xl70"]Michael[/TD]
[TD="class: xl71"]19-03-01[/TD]
[TD="class: xl72"] 20.00[/TD]
[TD="class: xl72"] [/TD]

[/tr]

[tr]

[TD="class: xl70"]Michael[/TD]
[TD="class: xl71"]19-03-01[/TD]
[TD="class: xl72"] 8.60[/TD]
[TD="class: xl72"] 28.60[/TD]

[/tr]

[tr]

[td]

Michael

[/td]

[TD="class: xl67"]19-03-02[/TD]
[TD="class: xl68"] 12.99[/TD]
[TD="class: xl69"] [/TD]

[/tr]

[tr]

[TD="class: xl70"]Michael[/TD]
[TD="class: xl71"]19-03-03[/TD]
[TD="class: xl72"] 13.55[/TD]
[TD="class: xl72"] [/TD]

[/tr]

[tr]

[TD="class: xl70"]Michael[/TD]
[TD="class: xl71"]19-03-03[/TD]
[TD="class: xl72"] 9.55[/TD]
[TD="class: xl72"] [/TD]

[/tr]

[tr]

[TD="class: xl70"]Michael[/TD]
[TD="class: xl71"]19-03-03[/TD]
[TD="class: xl72"] 3.99[/TD]
[TD="class: xl72"] 27.09[/TD]

[/tr]

[tr]

[td]

Michael

[/td]

[TD="class: xl67"]19-03-04[/TD]
[TD="class: xl68"] 12.99[/TD]
[TD="class: xl69"] [/TD]

[/tr]

[tr]

[td]

Michael

[/td]

[TD="class: xl67"]19-03-05[/TD]
[TD="class: xl68"] 15.99[/TD]
[TD="class: xl69"] [/TD]

[/tr]

[/TABLE]

**Carim** · Apr 4th 2019

Hello and Welcome to the Forum ...:smile:

Try to make your life easy ...

Just Insert a Pivot Table and use the feature which allows to Group by Date ...

Not only it will only take you 10 seconds ... but with the flexibility offered you will only need to refresh whenever data change ...

Hope this will help

LBDaye · Apr 4th 2019

Hi Carim,
Thank you for your solution! Up to this point where I want to sum up based on same date, I already run a macro that inserted 3 blank rows then add total amount for each individual account. However, I want to add more commands to this macro that sum the value in Column C based on same date in Column B. Then, list the result in Column D. Thanks again Carim!

**Carim** · Apr 4th 2019

Hi,

If you are already using a macro to produce this report ...

Why don't you post your macro ... to get a proposed modification ... ???

Even better ... why don't you attach a sample file with your current macro ...

LBDaye · Apr 5th 2019

Hi Carim
Here is the macro that I run:

As you can see, after running this macro, I have to manually sum up the amount in Column C based on same date in Column B, then display the result in Column D. Thank you very much for your time!

Dim lr As Long, r As Long, fr As Long
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Columns("B:B").Select
Selection.Replace What:=" *", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.NumberFormat = "yy-mm-dd"
Columns("B:B").Select
Selection.ColumnWidth = 11
Columns("B:D").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 3).Sort _
Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("C1"), _
Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
Columns("C:C").Select
Selection.Cut
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Columns("D:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("E8").Select
fr = 1
lr = Range("A" & Rows.Count).End(xlUp).Row
r = 1
Do While r <= lr
r = r + 1
If Cells(r, 1).Value <> Cells(r - 1, 1).Value Then
Rows(r).Resize(3).Insert
Cells(r, 3).FormulaR1C1 = "=SUM(R" & fr & "C:R[-1]C)"
r = r + 3
fr = r
lr = lr + 3
End If
Loop
Application.ScreenUpdating = True
End Sub

**Carim** · Apr 5th 2019

Hello,

Thanks for your macro.

Since there are many worksheet manipulations ( deleting Rows and Columns ...) it would make sense to attach a sample file .... :smile:

By the way, with the exception of the sorting section ... most of the macro is generated by the macro recorder .. and deserves some improvement ... :wink:

LBDaye · Apr 6th 2019

Hi Carim,below is the part of very long orginal file
[TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 239"]

[tr]

[TD="class: xl65, width: 64"]Alpha[/TD]
[TD="width: 126"]2019-02-25 20:46:20.981076-08[/TD]
[TD="width: 64"]AL000034[/TD]
[TD="width: 64, align: right"]7.99[/TD]

[/tr]

[tr]

[TD="class: xl65, width: 64"]Alpha[/TD]

[td]

2019-03-03 14:06:01.27398-08

[/td]

[td]

AL000035

[/td]

[TD="align: right"]15.99[/TD]

[/tr]

[tr]

[TD="class: xl65, width: 64"]Alpha[/TD]

[td]

2019-03-09 13:35:44.646709-08

[/td]

[td]

AL000036

[/td]

[TD="align: right"]3.44[/TD]

[/tr]

[tr]

[TD="class: xl65, width: 64"]Alpha[/TD]

[td]

2019-03-09 16:32:11.686707-08

[/td]

[td]

AL000037

[/td]

[TD="align: right"]4.99[/TD]

[/tr]

[tr]

[TD="class: xl65, width: 64"]Alpha[/TD]

[td]

2019-03-09 16:32:47.514488-08

[/td]

[td]

AL000038

[/td]

[TD="align: right"]14.99[/TD]

[/tr]

[tr]

[TD="class: xl65, width: 64"]Alpha[/TD]

[td]

2019-03-13 19:02:43.313838-07

[/td]

[td]

AL000039

[/td]

[TD="align: right"]7.99[/TD]

[/tr]

[tr]

[TD="class: xl65, width: 64"]Alpha[/TD]

[td]

2019-03-14 16:39:39.053635-07

[/td]

[td]

AL000040

[/td]

[TD="align: right"]18.99[/TD]

[/tr]

[tr]

[TD="class: xl65, width: 64"]Alpha[/TD]

[td]

2019-03-25 20:34:06.389776-07

[/td]

[td]

AL000041

[/td]

[TD="align: right"]22[/TD]

[/tr]

[tr]

[TD="class: xl65, width: 64"]Alpha[/TD]

[td]

2019-03-30 15:12:50.274899-07

[/td]

[td]

AL000042

[/td]

[TD="align: right"]19.99[/TD]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-02-25 15:13:35.809738-08

[/td]

[td]

MI000337

[/td]

[TD="align: right"]94.38[/TD]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-02-26 19:40:51.473973-08

[/td]

[td]

MI000338

[/td]

[TD="align: right"]10[/TD]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-02-27 10:15:38.881493-08

[/td]

[td]

MI000339

[/td]

[TD="align: right"]7.99[/TD]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-02-27 10:15:38.881493-08

[/td]

[td]

MI000340

[/td]

[TD="align: right"]9.99[/TD]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-02-27 10:15:38.881493-08

[/td]

[td]

MI000341

[/td]

[TD="align: right"]16.99[/TD]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-02-27 14:34:06.178665-08

[/td]

[td]

MI000342

[/td]

[TD="align: right"]31.95[/TD]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-02-27 19:57:18.684453-08

[/td]

[td]

MI000343

[/td]

[TD="align: right"]26.6[/TD]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-02-28 18:03:50.496687-08

[/td]

[td]

MI000344

[/td]

[TD="align: right"]17.95[/TD]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-02-28 18:05:15.119825-08

[/td]

[td]

MI000345

[/td]

[TD="align: right"]27.95[/TD]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-03-01 10:41:58.905819-08

[/td]

[td]

MI000346

[/td]

[TD="align: right"]20[/TD]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-03-01 13:43:22.492583-08

[/td]

[td]

MI000347

[/td]

[TD="align: right"]8.6[/TD]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-03-02 12:21:34.367801-08

[/td]

[td]

MI000348

[/td]

[TD="align: right"]12.99[/TD]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-03-03 13:26:16.572658-08

[/td]

[td]

MI000349

[/td]

[TD="align: right"]13.55[/TD]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-03-03 13:26:16.572658-08

[/td]

[td]

MI000350

[/td]

[TD="align: right"]9.55[/TD]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-03-03 16:07:12.673826-08

[/td]

[td]

MI000351

[/td]

[TD="align: right"]3.99[/TD]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-03-05 20:28:00.477949-08

[/td]

[td]

MI000352

[/td]

[TD="align: right"]12.99[/TD]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-03-05 20:28:46.418232-08

[/td]

[td]

MI000353

[/td]

[TD="align: right"]15.99[/TD]

[/tr]

[/TABLE]
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="class: xl63, width: 64"] [/TD]
[TD="width: 126"] [/TD]
[TD="width: 64"] [/TD]
[TD="width: 64, align: right"] [/TD]

[/tr]

[tr]

[TD="class: xl63, width: 64"] [/TD]

[td][/td]

[TD="align: right"] [/TD]

[/tr]

[tr]

[TD="class: xl63, width: 64"] [/TD]

[td][/td]

[TD="align: right"] [/TD]

[/tr]

[tr]

[TD="class: xl63, width: 64"] [/TD]

[td][/td]

[TD="align: right"] [/TD]

[/tr]

[tr]

[TD="class: xl63, width: 64"] [/TD]

[td][/td]

[TD="align: right"] [/TD]

[/tr]

[tr]

[TD="class: xl63, width: 64"] [/TD]

[td][/td]

[TD="align: right"] [/TD]

[/tr]

[tr]

[TD="class: xl63, width: 64"] [/TD]

[td][/td]

[TD="align: right"] [/TD]

[/tr]

[tr]

[TD="class: xl63, width: 64"] [/TD]

[td][/td]

[TD="align: right"] [/TD]

[/tr]

[tr]

[TD="class: xl63, width: 64"] [/TD]

[td][/td]

[TD="align: right"] [/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"] [/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"] [/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"] [/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"] [/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"] [/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"] [/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"] [/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"] [/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"] [/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"] [/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"] [/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"] [/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"] [/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"] [/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"] [/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"] [/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"] [/TD]

[/tr]

[/TABLE]

LBDaye · Apr 6th 2019

Hi Carim,
The first row I missed is added [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 287"]

[tr]

[TD="width: 64"]Owners[/TD]
[TD="width: 126"]Payment Time[/TD]
[TD="width: 64"]Receipt Code[/TD]
[TD="width: 128, colspan: 2"]Amount Paid[/TD]

[/tr]

[tr]

[TD="class: xl65, width: 64"]Alpha[/TD]

[td]

2019-02-25 20:46:20.981076-08

[/td]

[td]

AL000034

[/td]

[TD="align: right"]7.99[/TD]

[td][/td]

[/tr]

[tr]

[TD="class: xl65, width: 64"]Alpha[/TD]

[td]

2019-03-03 14:06:01.27398-08

[/td]

[td]

AL000035

[/td]

[TD="align: right"]15.99[/TD]

[td][/td]

[/tr]

[tr]

[TD="class: xl65, width: 64"]Alpha[/TD]

[td]

2019-03-09 13:35:44.646709-08

[/td]

[td]

AL000036

[/td]

[TD="align: right"]3.44[/TD]

[td][/td]

[/tr]

[tr]

[TD="class: xl65, width: 64"]Alpha[/TD]

[td]

2019-03-09 16:32:11.686707-08

[/td]

[td]

AL000037

[/td]

[TD="align: right"]4.99[/TD]

[td][/td]

[/tr]

[tr]

[TD="class: xl65, width: 64"]Alpha[/TD]

[td]

2019-03-09 16:32:47.514488-08

[/td]

[td]

AL000038

[/td]

[TD="align: right"]14.99[/TD]

[td][/td]

[/tr]

[tr]

[TD="class: xl65, width: 64"]Alpha[/TD]

[td]

2019-03-13 19:02:43.313838-07

[/td]

[td]

AL000039

[/td]

[TD="align: right"]7.99[/TD]

[td][/td]

[/tr]

[tr]

[TD="class: xl65, width: 64"]Alpha[/TD]

[td]

2019-03-14 16:39:39.053635-07

[/td]

[td]

AL000040

[/td]

[TD="align: right"]18.99[/TD]

[td][/td]

[/tr]

[tr]

[TD="class: xl65, width: 64"]Alpha[/TD]

[td]

2019-03-25 20:34:06.389776-07

[/td]

[td]

AL000041

[/td]

[TD="align: right"]22[/TD]

[td][/td]

[/tr]

[tr]

[TD="class: xl65, width: 64"]Alpha[/TD]

[td]

2019-03-30 15:12:50.274899-07

[/td]

[td]

AL000042

[/td]

[TD="align: right"]19.99[/TD]

[td][/td]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-02-25 15:13:35.809738-08

[/td]

[td]

MI000337

[/td]

[TD="align: right"]94.38[/TD]

[td][/td]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-02-26 19:40:51.473973-08

[/td]

[td]

MI000338

[/td]

[TD="align: right"]10[/TD]

[td][/td]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-02-27 10:15:38.881493-08

[/td]

[td]

MI000339

[/td]

[TD="align: right"]7.99[/TD]

[td][/td]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-02-27 10:15:38.881493-08

[/td]

[td]

MI000340

[/td]

[TD="align: right"]9.99[/TD]

[td][/td]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-02-27 10:15:38.881493-08

[/td]

[td]

MI000341

[/td]

[TD="align: right"]16.99[/TD]

[td][/td]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-02-27 14:34:06.178665-08

[/td]

[td]

MI000342

[/td]

[TD="align: right"]31.95[/TD]

[td][/td]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-02-27 19:57:18.684453-08

[/td]

[td]

MI000343

[/td]

[TD="align: right"]26.6[/TD]

[td][/td]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-02-28 18:03:50.496687-08

[/td]

[td]

MI000344

[/td]

[TD="align: right"]17.95[/TD]

[td][/td]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-02-28 18:05:15.119825-08

[/td]

[td]

MI000345

[/td]

[TD="align: right"]27.95[/TD]

[td][/td]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-03-01 10:41:58.905819-08

[/td]

[td]

MI000346

[/td]

[TD="align: right"]20[/TD]

[td][/td]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-03-01 13:43:22.492583-08

[/td]

[td]

MI000347

[/td]

[TD="align: right"]8.6[/TD]

[td][/td]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-03-02 12:21:34.367801-08

[/td]

[td]

MI000348

[/td]

[TD="align: right"]12.99[/TD]

[td][/td]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-03-03 13:26:16.572658-08

[/td]

[td]

MI000349

[/td]

[TD="align: right"]13.55[/TD]

[td][/td]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-03-03 13:26:16.572658-08

[/td]

[td]

MI000350

[/td]

[TD="align: right"]9.55[/TD]

[td][/td]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-03-03 16:07:12.673826-08

[/td]

[td]

MI000351

[/td]

[TD="align: right"]3.99[/TD]

[td][/td]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-03-05 20:28:00.477949-08

[/td]

[td]

MI000352

[/td]

[TD="align: right"]12.99[/TD]

[td][/td]

[/tr]

[tr]

[td]

Michael

[/td]

[td]

2019-03-05 20:28:46.418232-08

[/td]

[td]

MI000353

[/td]

[TD="align: right"]15.99[/TD]

[td][/td]

[/tr]

[/TABLE]

**KjBox** · Apr 7th 2019

Try the attached file. Click the button to generate Monthly Totals for each Owner.

Code assigned to the button:

Code

Sub MonthlyTotals()
    Dim x, i As Long, dTot As Double
    
    With ActiveSheet.Cells(1).CurrentRegion
        .Sort .Columns(2), , , , , , , 1
        .Sort .Columns(1), , , , , , , 1
        .Offset(1).Columns(5).ClearContents
        x = .Value2
        For i = 2 To UBound(x, 1) - 1
            If i < UBound(x, 1) - 1 Then
                If x(i, 1) = x(i + 1, 1) _
                And Month(CDate(x(i, 2))) = Month(CDate(x(i + 1, 2))) Then
                    dTot = dTot + x(i, 4)
                Else
                    x(i, 5) = dTot + x(i, 4)
                    dTot = 0
                End If
            ElseIf x(i, 1) = x(i + 1, 1) _
                And Month(CDate(x(i, 2))) = Month(CDate(x(i + 1, 2))) Then
                x(i + 1, 5) = dTot + x(i, 4) + x(i + 1, 4)
            Else
                x(i, 5) = dTot + x(i, 4)
                x(i + 1, 5) = x(i + 1, 4)
            End If
        Next
        .Value2 = x
        .Offset(1).Columns(5).SpecialCells(2).Font.Bold = 1
    End With
    
End Sub

Display More

The first bit of the code is to ensure that the data is sorted by Owner and Payment Time.

Note you must include the new header in cell E1 when transferring the code to your actual data.

**Carim** · Apr 7th 2019

KjBox

Hello,

Nice Macro ... :smile:

Obviously do not know why ... but for any OP .... attaching a sample file remains a psychological hurdle .....

Even if I am still convinced a Pivot Table would produce the expected results in less than 5 seconds ... does not matter ...

Question : why .Value2 ?

Thought Array variants were not format sensitive ...

Could it prevent local configuration conflicts with Dates ?

Thanks for your insight ...

**KjBox** · Apr 7th 2019

Hi Carim,

Thanks.

Value2 gives a cells underlying value (so dates as their Long values). Then by using CDate in the code that date is converted to Date format in whatever Locale settings the user has, this overcomes the issue of code been written using a particular Locale setting then that code failing for a user with a different Locale setting.

In actual fact, .Value2 is better than .Value for any data type in over 90% of cases.

For a full explanation of the differences between .Text, .Value and .Value2 see https://fastexcel.wordpress.com/2011...w-to-avoid-it/

You are right about using a Pivot Table, but the OP asked for code to produce the illustrated result, maybe that works better for him in his situation.

Having said that, attached is the sample file with the data in an Excel Built-in table, my code modified to suit the new table format and a Pivot Table that gives the same results.

I have added code in the Worksheet Object Module which will update the Pivot Table when change(s) to the data are made

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [tblPayments]) Is Nothing Then ThisWorkbook.RefreshAll
End Sub

**Carim** · Apr 7th 2019

Hi Charles :smile:

Thanks for the link to Charles ' site ...

Interesting to learn that Value2 is faster Value ... !!!

Take care

:smile:

**KjBox** · Apr 8th 2019

Value2 was added to Excel around 10 years ago, but Value remained (and still does) as the default property of a range. IMO the default should be Value2.

**Carim** · Apr 8th 2019

Quote from KjBox

Value2 was added to Excel around 10 years ago, but Value remained (and still does) as the default property of a range. IMO the default should be Value2.

You are right ...!!!

No doubt about this ...

Thanks again

:smile:

LBDaye · Apr 9th 2019

Thank you so much for your time KjBox and Carim!
I created a blank macro then pasted your code. However, I got the message:"Run time error (9), Subscript out of range. And, when I hit debug, it highlighted
x(i, 5) = dTot + x(i, 4) Am I doing somethings wrong? Greatly appreciate your help!

**Carim** · Apr 9th 2019

Hello,

As KjBox pointed out .... before launching the macro ... make sure to have a Header in cell E1 ...

Hope this will help

**KjBox** · Apr 9th 2019

Thanks Carim, I was going to suggest the same thing! Most likely source of the error.

LBDaye · Apr 9th 2019

Thank you for all your help! I greatly appreciate KjBox and Carim who have dedicated their precious time to help community!!! Just let you know that I have read all your solution but don't have time to try your solutions yet. Will let you updated. Just quick glance of the solution and get confused with: "before launching the macro ... make sure to have a Header in cell E1 ..."
Header in cell E1...What exactly this mean Carim?

**KjBox** · Apr 9th 2019

If you look at the test file I attached you will see that in cell E1 I put "Monthly Totals".

You need to do the same to your actual file before running the macro.

LBDaye · Apr 10th 2019

Hi KjBox and Carim,

As we discussed, I just list some of the owners to the list. The actual list is about 600 rows. So when I added more rows to the sample you provided, I got the message:"Run-time error '13' Type mismatch. Any advise? Thanks.

Sum values in a column based on date

Participate now!

Share