Macro to sum array by date
 

Hi,

I am having some trouble with this marco that I'm attempting. Hoping
someone could give me a hand. I am trying to sum an array based on a
date range.

Here is how I am passing info to the macro:

=accmgrsum(1/1/05,1/31/05,Bookings!D3:D7,Bookings!H3:H7)

Bookings being a worksheet. Bookings!D3:D7 are dates and
Bookings!H3:H7 are dollars.

Here is my marco:

Function accmgrsum(start_date, end_date, date_array, bookings_array)
accmgrsum = Application.Sum(IIf(date_array =
Application.DateValue("start_date"), IIf(date_array <=
Application.DateValue("end_date"), bookings_array, 0), 0))
End Function

Could someone let me know where I have gone wrong? I am not too well
versed on VBA as you can probably tell. Am I on the right track or is
there an easier way?

Thanks,
Joe

=SUMPRODUCT(--(Bookings!D3:D7=--"2005-01-01"),--(Bookings!D3:D7<=--"2005-01
-31"),Bookings!H3:H7)

No VBA!

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Joe Blow" wrote in message
...

Hi,

I am having some trouble with this marco that I'm attempting. Hoping
someone could give me a hand. I am trying to sum an array based on a
date range.

Here is how I am passing info to the macro:

=accmgrsum(1/1/05,1/31/05,Bookings!D3:D7,Bookings!H3:H7)

Bookings being a worksheet. Bookings!D3:D7 are dates and
Bookings!H3:H7 are dollars.

Here is my marco:

Function accmgrsum(start_date, end_date, date_array, bookings_array)
accmgrsum = Application.Sum(IIf(date_array =
Application.DateValue("start_date"), IIf(date_array <=
Application.DateValue("end_date"), bookings_array, 0), 0))
End Function

Could someone let me know where I have gone wrong? I am not too well
versed on VBA as you can probably tell. Am I on the right track or is
there an easier way?

Thanks,
Joe

On Mon, 17 Jan 2005 21:30:44 -0000, "Bob Phillips"
wrote:

=SUMPRODUCT(--(Bookings!D3:D7=--"2005-01-01"),--(Bookings!D3:D7<=--"2005-01
-31"),Bookings!H3:H7)

No VBA!

Thanks Bob, the reason I am using vba is that I need to call this
macro repeatedly with many differing dates. This is just my test case.
Any ideas?

Joe

Joe,

Saw you in the torrents group today and remembered I hadn't answered your
post. Here is a solution

Function accmgrsum(start_date, end_date, date_array, bookings_array)
Dim sFormula As String

sFormula = "SUMPRODUCT(--(" & date_array.Address(external:=True) & _
"=--""" & Format(CDate(start_date), "yyyy-mm-dd")
& """)," & _
"--(" & date_array.Address(external:=True) & _
"<=--""" & Format(CDate(end_date), "yyyy-mm-dd") &
""")," & _
bookings_array.Address(external:=True) & ")"
accmgrsum = Evaluate(sFormula)
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Joe Blow" wrote in message
...
On Mon, 17 Jan 2005 21:30:44 -0000, "Bob Phillips"
wrote:


=SUMPRODUCT(--(Bookings!D3:D7=--"2005-01-01"),--(Bookings!D3:D7<=--"2005-0
1
-31"),Bookings!H3:H7)

No VBA!

Thanks Bob, the reason I am using vba is that I need to call this
macro repeatedly with many differing dates. This is just my test case.
Any ideas?

Joe

On Wed, 19 Jan 2005 20:21:04 -0000, "Bob Phillips"
wrote:

Joe,

Saw you in the torrents group today and remembered I hadn't answered your
post. Here is a solution

Function accmgrsum(start_date, end_date, date_array, bookings_array)
Dim sFormula As String

sFormula = "SUMPRODUCT(--(" & date_array.Address(external:=True) & _
"=--""" & Format(CDate(start_date), "yyyy-mm-dd")
& """)," & _
"--(" & date_array.Address(external:=True) & _
"<=--""" & Format(CDate(end_date), "yyyy-mm-dd") &
""")," & _
bookings_array.Address(external:=True) & ")"
accmgrsum = Evaluate(sFormula)
End Function

Bob,

Many thanks! Looks quite different from my first pass. I'll give it a
try later today.

Regards,
Joe

Yeah, whereas you tried to do it all in VB, I just used builtin Excel
functionality. I had the biggest problem with the dates, the formula worked
in Excel, but not when I transposed to VBA. That is why I cast the string
to a date, and formatted it backwards.

Bob


"Joe Blow" wrote in message
...
On Wed, 19 Jan 2005 20:21:04 -0000, "Bob Phillips"
wrote:

Joe,

Saw you in the torrents group today and remembered I hadn't answered your
post. Here is a solution

Function accmgrsum(start_date, end_date, date_array, bookings_array)
Dim sFormula As String

sFormula = "SUMPRODUCT(--(" & date_array.Address(external:=True) & _
"=--""" & Format(CDate(start_date),
"yyyy-mm-dd")
& """)," & _
"--(" & date_array.Address(external:=True) & _
"<=--""" & Format(CDate(end_date), "yyyy-mm-dd")
&
""")," & _
bookings_array.Address(external:=True) & ")"
accmgrsum = Evaluate(sFormula)
End Function

Bob,

Many thanks! Looks quite different from my first pass. I'll give it a
try later today.

Regards,
Joe