Can a date range be used in SUMPRODUCT
 

I have a Check Register Budgeting project. I want to total all of the checks
that cleared within a particular month for a particular accout. Each check
is recorded on a separate row so that Register!B4:B90 is the range that
contains the date the check cleared, Register!I4:I90 contains the
account/budget ID, and Register!D4:D90 contains the amount the check was
written for that I want to total.
This is what I have tried so far to total only one date (01/15/2008):

=SUMPRODUCT(--(Register!B4:B9=DATE(2008,01,15)),--(Register!I4:I9=G5),Register!D4:D9)

The formula above works, but when I try to replace the first array with a
date range like this it blow up:

=SUMPRODUCT(--(DATE(2007,12,31) < Register!B4:B90 <
DATE(2008,02,01)),--(Register!I4:I90="ACCOUNT#1"),Register!D4:D90)

Any suggestions?

Can a date range be used in SUMPRODUCT
 

Try


=SUMPRODUCT(--(Register!B4:B90DATE(2007,12,31)),--(Register!B4:B90=DATE(2008,02,01)),--(Register!I4:I90="ACCOUNT#1"),Register!D4:D90)


or


=SUMPRODUCT(--(YEAR(Register!B4:B90)=2007),--(MONTH(Register!B4:B90)=1),--(Register!I4:I90="ACCOUNT#1"),Register!D4:D90)


--


Regards,


Peo Sjoblom





"bruce" wrote in message
...
I have a Check Register Budgeting project. I want to total all of the
checks
that cleared within a particular month for a particular accout. Each
check
is recorded on a separate row so that Register!B4:B90 is the range that
contains the date the check cleared, Register!I4:I90 contains the
account/budget ID, and Register!D4:D90 contains the amount the check was
written for that I want to total.
This is what I have tried so far to total only one date (01/15/2008):

=SUMPRODUCT(--(Register!B4:B9=DATE(2008,01,15)),--(Register!I4:I9=G5),Register!D4:D9)

The formula above works, but when I try to replace the first array with a
date range like this it blow up:

=SUMPRODUCT(--(DATE(2007,12,31) < Register!B4:B90 <
DATE(2008,02,01)),--(Register!I4:I90="ACCOUNT#1"),Register!D4:D90)

Any suggestions?

Can a date range be used in SUMPRODUCT
 

Thanks! That did the trick.

"Peo Sjoblom" wrote:

Try


=SUMPRODUCT(--(Register!B4:B90DATE(2007,12,31)),--(Register!B4:B90=DATE(2008,02,01)),--(Register!I4:I90="ACCOUNT#1"),Register!D4:D90)


or


=SUMPRODUCT(--(YEAR(Register!B4:B90)=2007),--(MONTH(Register!B4:B90)=1),--(Register!I4:I90="ACCOUNT#1"),Register!D4:D90)


--


Regards,


Peo Sjoblom





"bruce" wrote in message
...
I have a Check Register Budgeting project. I want to total all of the
checks
that cleared within a particular month for a particular accout. Each
check
is recorded on a separate row so that Register!B4:B90 is the range that
contains the date the check cleared, Register!I4:I90 contains the
account/budget ID, and Register!D4:D90 contains the amount the check was
written for that I want to total.
This is what I have tried so far to total only one date (01/15/2008):

=SUMPRODUCT(--(Register!B4:B9=DATE(2008,01,15)),--(Register!I4:I9=G5),Register!D4:D9)

The formula above works, but when I try to replace the first array with a
date range like this it blow up:

=SUMPRODUCT(--(DATE(2007,12,31) < Register!B4:B90 <
DATE(2008,02,01)),--(Register!I4:I90="ACCOUNT#1"),Register!D4:D90)

Any suggestions?