 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / July 2008Sum 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 =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)  SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi... > [quoted text clipped - 28 lines] > Thanks in advance! > Rob 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? > =SUMPRODUCT(--(YEAR(Australia!$A$2:$A$2000)=YEAR(Summary!C$3)), > --(MONTH(Australia!$A$2:$A$2000)=MONTH(Summary!C$3)), [quoted text clipped - 32 lines] > > Thanks in advance! > > Rob 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")) > Thanks! > [quoted text clipped - 37 lines] > > > Thanks in advance! > > > Rob Sorry, I did think of that but forgot to include it :-) SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Nevermind I got it! > [quoted text clipped - 47 lines] >> > > Thanks in advance! >> > > Rob |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.