Sum values between two dates by VBA code

Topic

New Reply

I hope someone will guide me to write the VBA code without showing formulas in the result cell to get the below answer

In the below dropbox linked file:

Column A8:A1000 I have production date (1-1-2015 to 31-12-2017)Column B8:B1000 I have Sales date (1-1-2015 to 31-12-2017)Column C8:C1000 I have Production QuantityColumn D8:D1000 I have Sales Quantity In cell E2 Start date is 3-3-2015In cell E3 End date is 9-9-2017In cell E4 i need a total of column C, in between the D2 and D3 dates of Column A (Production quantity between those dates)In cell E5 i need a total of column D, in between the D2 and D3 dates of Column B (Sales quantity between those dates)

https://www.dropbox.com/s/ifdak1vx82xgb5w/Sum%20Between%20two%20dates.xlsx?dl=0

Edit to remove HTML. Please use the “Text” tab in the entry box when you copy/paste.

Reply | Quote

Replies

I don’t profess to be a VBA expert, but I think this does what you want:

Code:

Sub SumBetween()
Sheets("Sheet1").Range("E4").Formula = "=SUMPRODUCT((A8:A15000>E2)*(A8:A15000<E3)*(C8:C15000))"
Sheets("Sheet1").Range("E4") = Sheets("Sheet1").Range("E4").Value
Sheets("Sheet1").Range("E5").Formula = "=SUMPRODUCT((B8:B15000>E2)*(B8:B15000<E3)*(D8:D15000))"
Sheets("Sheet1").Range("E5") = Sheets("Sheet1").Range("E5").Value
End Sub

1 user thanked author for this post.

zeddy

Reply | Quote

This is not meant to dissuade your pursuit of a solution in VBA but for my simpler needs I tend to rely upon the Julian calendar where each day in the year had a number, 1 to 365 or 1 to 366, an example would be rather simple to use current day of 126 and subtract a previous day such as 85 to get the difference from March 26.  The US Government and Military always had the Julian date included on their calendars, don’t know if they ever stopped that practice since I retired 25 years ago.

Before you wonder "Am I doing things right," ask "Am I doing the right things?"

Reply | Quote

Or if you’re interested in a non VBA solution these formulas will do the trick.

e4:=SUMIFS(C8:C5187,A8:A5187,">="&E2,A8:A5187,"<="&E3)
e5:=SUMIFS(D8:D5187,B8:B5187,">="&E2,B8:B5187,"<="&E3)

Note: Your sample file had dates way past row 1000 so I used the rows that contained dates. I'd recommend using dynamic range names so that when you add rows the ranges are automatically adjusted. I'd also assign range names to the beginning & ending dates.

HTH :cool:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hey, RG…glad to see you’re logging in!!

BTW: if the OP still wanted VBA, SUMIFS doesn’t work there, does it? I didn’t think so, which is why I used SUMPRODUCT.  I guess you can use Application.WorksheetFunction.SUMIFS…right?

Oh, and I took him literally when he said “between” and didn’t include the =.

Reply | Quote

Kevin.

You know I love VBA but it isn’t always the best solution. If the OP doesn’t want the formulas to show he could protect those cells then protect the sheet uncheck the option allowing the selection of protected cells thereby hiding the formulas.

HTH

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

1 user thanked author for this post.

woody

Reply | Quote

krweaver wrote:

Hey, RG…glad to see you’re logging in!!

BTW: if the OP still wanted VBA, SUMIFS doesn’t work there, does it? I didn’t think so, which is why I used SUMPRODUCT.  I guess you can use Application.WorksheetFunction.SUMIFS…right?

Oh, and I took him literally when he said “between” and didn’t include the =.

FWIW, you could use SUMIFS equally as well as you used SUMPRODUCT there. It’s just a formula.

Reply | Quote

Dear All

The below code is working fine except when I select (“EN6”) value in the cell (“P1”) dropdown list because it is an index match formula and all others are sumproduct. The sumproduct I got results after many website searching.

Just go through the code and give me a solution to get the results when I select EN6 value in the cell P1.

Thanks in advance
 

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim J As Range, G As Range, Where As Range

Dim Low As Date, High As Date

Dim Dates, Types, Values

Dim i As Long

Dim Sum As Double, Sum1 As Double, Sum2 As Double

 

'Only if P1 changes

If Intersect(Target, Range("P1")) Is Nothing Then Exit Sub

 

'Get the dates

Low = Range("EO1").Value

High = Range("EN1").Value

'Refer to the used cells in column J

Set J = Range("J8", Range("J" & Rows.Count).End(xlUp))

'Same size in column G

Set G = Intersect(Columns("G"), J.EntireRow)

'Read in all data

Dates = J.Value

Types = G.Value

 

Select Case Target.Value

Case Range("EN2").Value

'Same size in column CF

Set Where = Intersect(Columns("CF"), J.EntireRow)

'Read in all values

Values = Where.Value

'Process the SUMPRODUCT

For i = 1 To UBound(Dates)

If Dates(i, 1) >= Low And Dates(i, 1) <= High And Types(i, 1) = "Sales" Then

Sum = Sum + Values(i, 1)

End If

Next

 

Case Range("EN3").Value

Set Where = Intersect(Columns("T"), J.EntireRow)

Values = Where.Value

For i = 1 To UBound(Dates)

If Dates(i, 1) >= Low And Dates(i, 1) <= High And Types(i, 1) = "Sales" Then

Sum = Sum + Values(i, 1)

End If

Next

 

Case Range("EN4").Value

Set Where = Intersect(Columns("B"), J.EntireRow)

Dates = Where.Value

Set Where = Intersect(Columns("CF"), J.EntireRow)

Values = Where.Value

 

For i = 1 To UBound(Dates)

If Dates(i, 1) <= High Then

Sum1 = Sum1 + Values(i, 1)

End If

Next

 

Set Where = Intersect(Columns("CA"), J.EntireRow)

Values = Where.Value

Dates = J.Value

 

For i = 1 To UBound(Dates)

If Dates(i, 1) <= High Then

Sum2 = Sum2 + Values(i, 1)

End If

Next

Sum = Sum1 - Sum2

 

Case Range("EN5").Value

Set Where = Intersect(Columns("BY"), J.EntireRow)

Values = Where.Value

'Process the SUMPRODUCT

For i = 1 To UBound(Dates)

If Dates(i, 1) >= Low And Dates(i, 1) <= High And Types(i, 1) = "Sales" Then

Sum = Sum + Values(i, 1)

End If

Next

 

Case Range("EN6").Value

Dim CG4EF4

CG4EF4 = Range("CG4:EF4").Value

With Worksheets("RM Price")

'Note the . in front of Range:

Set Where = .Range("A2", .Range("A" & .Rows.Count).End(xlUp))

Set Where = Intersect(.Columns("B:BA"), Where.EntireRow)

End With

Values = Where.Value

Case Range("EN7").Value

Set Where = Intersect(Columns("B"), J.EntireRow)

Dates = Where.Value

Set Where = Intersect(Columns("CE"), J.EntireRow)

Values = Where.Value

 

For i = 1 To UBound(Dates)

If Dates(i, 1) <= High Then

Sum1 = Sum1 + Values(i, 1)

End If

Next

 

Set Where = Intersect(Columns("BZ"), J.EntireRow)

Values = Where.Value

Dates = J.Value

 

For i = 1 To UBound(Dates)

If Dates(i, 1) <= High Then

Sum2 = Sum2 + Values(i, 1)

End If

Next

Sum = Sum1 - Sum2

End Select

 

'Events off, otherwise we call ourself

Application.EnableEvents = False

'Write the sum into the sheet

Range("EL7") = Sum

'Events on

Application.EnableEvents = True

End Sub

Reply | Quote

How about something like this:

Public Sub SumProdSales()
Dim I As Long
Range("E4") = ""
Range("E5") = ""
LR1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
LR2 = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
If LR1 >= LR2 Then LR = LR1 Else LR = LR2
For I = 8 To LR
If Cells(I, 1) >= Range("E2") And Cells(I, 1) <= Range("E3") Then
Range("E4") = Range("E4") + Cells(I, 3)
End If
If Cells(I, 2) >= Range("E2") And Cells(I, 1) <= Range("E3") Then
Range("E5") = Range("E5") + Cells(I, 4)
End If
Next I
End Sub

HTH,

Maud

1 user thanked author for this post.

zeddy

Reply | Quote

See attached file

zeddy

Reply | Quote

In my attached file in the previous post, ignore the .pdf file extension, it’s really an excel .xlsb file. But you can’t post .xlsb file types here, so apologies for tricking the system.

In the posted file, I used the worksheet change event to detect if the Start Date or End Date was changed. If a date is changed, the result is then re-calculated using vba.

My calc formula uses the entire columns. It is simple, and effective.

zeddy

Reply | Quote

#zeddy
I don’t believe the attachment of the file was successful.
I’m not seeing any attachment.

1 user thanked author for this post.

zeddy

Reply | Quote

Ignore my previous 2 posts – seems like I didn’t trick the system after all!

So I will now try and work out how to post the block of vba code I used.

I tried to wrap it using

Code:

 and 

but the system here didn’t like that.

 

zeddy

Reply | Quote

Ok I’ll try this:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, [e2:e3]) Is Nothing Then       'changed date cell detected
zProductionQty = [=SUMIFS(C:C,A:A,">="&E2,A:A,"<="&E3)] 'calculate value
zSalesQty = [=SUMIFS(D:D,B:B,">="&E2,B:B,"<="&E3)]      'calculate value
[e4] = zProductionQty                                   'put value in cell
[e5] = zSalesQty                                        'put value in cell
End If

End Sub

zeddy

 

Reply | Quote

In my previous post, put that into the sheet-code-module.

 

zeddy

Reply | Quote

[PKCano]

Try using:
< pre >
< /pre >

without the spaces.

• This reply was modified 5 years, 10 months ago by PKCano.

Reply | Quote

Ok, trying to post a copy of the .xlsx file without the sheet code.

Sum-Between-two-dates-rz1

Reply | Quote

OK, trying again

to attach the camouflaged .xlsb file as a “pdf” file..

zeddy

Reply | Quote

[zeddy]

It seems we can’t post any excel .xlsm files.

This is a pity, it would be more convenient. And if the reasoning is because people are worried about macro viruses, then I would say get yourself a virus protection program. They are available. Just saying. It is tedious to have to post code that has to be then copied into files.

I liked Kevin’s method of putting a formula into the cells via vba, then converting the formulas to values, as the OP didn’t want to see any formulas. A nice touch to use 15000 in the formulas to allow for plenty of extra record rows. But since you can now refer to entire columns in formulas, without incurring unwanted calc overhead (only used-range-rows are actually used), you can effectively allow for over 1 million record rows.

I liked RG’s suggestion about using dynamic range names, but using entire-column references in the SUMIFS formulas means you don’t need to. Using sheet protection to lock and hide the formula cells is a really great suggestion – it would stop anyone just overtyping the results-cells to put whatever values they ‘wanted to see’.

Maudibe’s nice vba routine is easy to follow, and you can understand what’s going on.

zeddy

• This reply was modified 5 years, 10 months ago by zeddy.

1 user thanked author for this post.

RetiredGeek

Reply | Quote

Zeddy,

Thanks for the heads up on using whole column references in SUMIF, I was unaware of that change. Guess it’s time to hit the documentation and see what other changes I have missed…LOL.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

I’m hitting the documentation too!

I’m only now getting used to the term “spill” for Excel. The <b>spill range</b> relates to the new output ranges of Dynamic Array formulas, which are replacing Ctrl+Shift+Enter (CSE) formulas. If something is blocking the output spill range, you will see a new #error type: #SPILL!

And we can refer to these spill ranges e.g. when defining the Source range for a Data Validation list, e.g. Source: =F2#

F2# refers to the entire range of spill data. F2 is the first cell in the spill range. Adding the # to the cell ref turns it into a reference for the entire spill range.

These new functions are not yet available on general release, but you can read up on them to get a step ahead.

zeddy

Carp Diem

(Fish of the day)

Reply | Quote