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