|
Sum multiple worksheet sales data by month
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 |
Sum multiple worksheet sales data by month
=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 |
Sum multiple worksheet sales data by month
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 |
Sum multiple worksheet sales data by month
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 |
Sum multiple worksheet sales data by month
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 |
| All times are GMT +1. The time now is 08:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com