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


--
andyp161
------------------------------------------------------------------------
andyp161's Profile: http://www.excelforum.com/member.php...o&userid=11654
View this thread: http://www.excelforum.com/showthread...hreadid=520548

=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" wrote in
message ...

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


--
andyp161
------------------------------------------------------------------------
andyp161's Profile: http://www.excelforum.com/member.php...o&userid=11654
View this thread: http://www.excelforum.com/showthread...hreadid=520548

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" wrote in
message ...

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