Sum multiple worksheet sales data by month

[RobS]

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

 

[Bob Phillips]

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

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?

 

[RobS]

Nevermind I got it!

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

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

 

[Bob Phillips]

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)