Sorry, I did think of that but forgot to include it :-)
--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"RobS" wrote in message
...
Nevermind I got it!

SUMPRODUCT(--(YEAR(INDIRECT("'"&$A5&"'!$A$2:$A$5000"))=YEAR(Sum mary!C$3)),

--(MONTH(INDIRECT("'"&$A5&"'!$A$2:$A$5000"))=MONTH(S ummary!C$3)),
INDIRECT("'"&$A5&"'!$E$2:$E$5000"))

"RobS" wrote:

Thanks!

That's most of it. Now how do I reference the first column of the
summary
sheet to grab the correct territory's worksheet name?

"Bob Phillips" wrote:

=SUMPRODUCT(--(YEAR(Australia!$A$2:$A$2000)=YEAR(Summary!C$3)),

--(MONTH(Australia!$A$2:$A$2000)=MONTH(Summary!C$3)) ,
Australia!$E$2:$E$2000)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"RobS" wrote in message
...
Hi...

I'm making a summary worksheet for raw transactional sales data
seperated
across multiple worksheets by territory.

- The worksheets are labelled by country and I'd like the formula to
reference the country name in a different cell
- I'm trying to sum by month
- The data spans multiple years
- I'm trying to use sumproduct and getting a #VALUE error

The summary worksheet looks like this:

Jun 08 Jul 08
Sales Sales
Australia
Austria
Canada
Dubai

The Sales data looks like this:

Date Sales
08-20-08 22.50
08-22-08 255.21
09-15-08 2211.51

This is where I am now:
SUMPRODUCT(--(YEAR(Australia!A1:A2000)=YEAR(Summary!C3)),--(MONTH(Australia!A1:A2000)=MONTH(Summary!C3)),--(Australia!E1:E2000))

Thanks in advance!
Rob