| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Consolidating Sales Data to Summary Worksheet | Excel Worksheet Functions | |||
| Where can I find a multiple product worksheet for a sales route? | New Users to Excel | |||
| projecting sales for month | New Users to Excel | |||
| Month to date sales - reset in new month??? | Excel Worksheet Functions | |||
Hybrid Mode
