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

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

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.

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:

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 😎

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.

How about something like this:

HTH,

Maud

See attached file

zeddy

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

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

 and 

but the system here didn’t like that.

zeddy

Ok I’ll try this:

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

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

zeddy

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

Sum-Between-two-dates-rz1

OK, trying again

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

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

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)