Sumproduct Datevalue Problem

  • Thread starter Thread starter andyp161
  • Start date Start date
A

andyp161

Hi there,

I have written the following formula, which works fine:

=SUMPRODUCT((Sheet1!$S$2:$S$27488=DATEVALUE("31/3/2006"))*(Sheet1!$O$2:$O$27488=(Sheet2!$A3)))

However, I actually want to return values for whole months rather tha
specific dates ie replace "31/03/2006" with "March".

Hope you can help.

Many thank
 
=SUMPRODUCT(--(MONTH(Sheet1!$S$2:$S$27488=3)),--(YEAR(Sheet1!$S$2:$S$27488=2
006)),--(Sheet1!$O$2:$O$27488=Sheet2!$A3))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Thanks Bob.

Unfortunatety, this returns 435,100 which is an impossible result. I
can't seem to work out why it isn't working. Any ideas?

Many thanks
 
Andy,

Brackets in wrong place

=SUMPRODUCT(--(MONTH(Sheet1!$S$2:$S$27488)=3),--(YEAR(Sheet1!$S$2:$S$27488)=
2006),--(Sheet1!$O$2:$O$27488=Sheet2!$A3))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Back
Top