Sumproduct Datevalue Problem

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 than specific dates ie replace "31/03/2006" with "March".

Hope you can help.

Many thanks

=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)

"andyp161" <[email protected]> wrote in
message news:[email protected]...
>
> 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$27
488=(Sheet2!$A3)))
>
> However, I actually want to return values for whole months rather than
> specific dates ie replace "31/03/2006" with "March".
>
> Hope you can help.
>
> Many thanks
>
>
> --
> andyp161
> ------------------------------------------------------------------------
> andyp161's Profile:
http://www.excelforum.com/member.php...o&userid=11654
> View this thread: http://www.excelforum.com/showthread...hreadid=520548
>

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)

"andyp161" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> andyp161
> ------------------------------------------------------------------------
> andyp161's Profile:
http://www.excelforum.com/member.php...o&userid=11654
> View this thread: http://www.excelforum.com/showthread...hreadid=520548
>