Can a date range be used in SUMPRODUCT

B

bruce

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?
 
P

Peo Sjoblom

Try


=SUMPRODUCT(--(Register!B4:B90>DATE(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
 
B

bruce

Thanks! That did the trick.

Peo Sjoblom said:
Try


=SUMPRODUCT(--(Register!B4:B90>DATE(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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top