[HELP] How to make Sheet1 auto gather data from Sheet2 ? ..more.. pls read...

Hi all, I am a new user in this forum, and yes I admit that I joined this cuz I need some Excel help...hope you all won't mind helping me here.

Well here is the setup

Sheet 1,
A1 "1/1/2004" (as in Jan 1st, 04)
B1 "2/1/2004" (as in Feb 1st, 04)
C1" 3/1/2004",
all the way to L1 with "12/1/2004" as in Dec 1st, 04

Sheet 2,

column A has about 1000 random dates wthin 2003 and 2004
column B has some random numbers.

What I want to do is .. in Sheet 1, in A2, to display the sum of those Sheet 2, column B numbers where corresponding column A is in the same month/year as A1

In pesudo code, it is like

$TOTAL=0
Foreach $ROW (Sheet 2.Column A)
.....IF $ROW = same month/year as Sheet1.A1
..........$TOTOL = $TOTAL + Sheet2.Column B.Row $ROW
......FI
ORF
Sheet 1.A2=$TOTAL

I have to admit that I know nothing about Macro or VB programming..

So, please let me know if this is doable ... or it require some other setup ?

I have uploaded an Excel file to my ftp in case my explanation is not clear...

The excel file is here..

http://home.comcast.net/~blk.hawk/ex...lp_example.xls

hope I didn't violated any forum rules...

and Thank a bunch in advance !!

BH

thx for reply ..

I tried the forumla... in A2 of the one I have attached..

I got 0 ......

meanwhile.. I am reverse studying the formula you provide... hope I can use that as a start point...

anyways... any other suggestions ?

thx

wow... got it ! thx Duane...

I modified the fomula to

=SUMPRODUCT((MONTH(Sheet2!A1:A2000)=MONTH(A1))*(YEAR(Sheet2!A1:A2000)=YEAR(A1))*(Sheet2!B1:B2000))


I changed the month and year comparision to MONTH(A1) instead of A2. . and it is work now !! cool !

thx