 |
 | 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 / June 2007Extracting data inexcell 2003
I have 2 spreadsheets, one listing a list of sales, the other showing a summary of sales by each month. how do i get the summary from the detailed list by month. Assuming there is a date associated with each sale: =SUMPRODUCT(--(MONTH(A2:A100)=6),B2:B100) Column A contains Dates Column B contains Sales Above give sales for June (ignoring year) =SUMPRODUCT(--(MONTH(A2:A100)=6),--(YEAR(A2:A100)=2007),B2:B100) Above give sales for June 2007 HTH > I have 2 spreadsheets, one listing a list of sales, the other showing a > summary of sales by each month. how do i get the summary from the detailed > list by month. Thank you very much I will give it a try. Thanks again. > Assuming there is a date associated with each sale: > [quoted text clipped - 14 lines] > > summary of sales by each month. how do i get the summary from the detailed > > list by month. Hi Toppers Thanks a lot but is the syntax correct as I keep getting formula errors. regards john > Assuming there is a date associated with each sale: > [quoted text clipped - 14 lines] > > summary of sales by each month. how do i get the summary from the detailed > > list by month. If you don't want to stretch the clairvoyant powers of the group, it might be handy if you tell us what error message you're getting. Where in the formula does the cursor sit when Excel reports the error? What do your windows regional settings use for separators? Are you sure that lists are separated by commas, or might they be semi-colons in your system?  SignatureDavid Biddulph > Hi Toppers > Thanks a lot but is the syntax correct as I keep getting formula errors. > regards > john >> Assuming there is a date associated with each sale: >> [quoted text clipped - 10 lines] >> >> HTH >> > I have 2 spreadsheets, one listing a list of sales, the other showing a >> > summary of sales by each month. how do i get the summary from the >> > detailed >> > list by month. I have now sorted it out. Thanks very much foe all your help. John > If you don't want to stretch the clairvoyant powers of the group, it might > be handy if you tell us what error message you're getting. Where in the [quoted text clipped - 27 lines] > >> > detailed > >> > list by month. Glad it's sorted. Perhaps you could let us know why Toppers' formula wasn't working for you and how you solved it, for the benefit of the group archive and other readers who may have similar problems? SignatureDavid Biddulph >I have now sorted it out. > Thanks very much foe all your help. > John >> If you don't want to stretch the clairvoyant powers of the group, it >> might [quoted text clipped - 4 lines] >> that lists are separated by commas, or might they be semi-colons in your >> system? >> > Hi Toppers >> > Thanks a lot but is the syntax correct as I keep getting formula >> > errors. >> >> Assuming there is a date associated with each sale: >> >> [quoted text clipped - 8 lines] >> >> >> >> Above give sales for June 2007 >> >> > I have 2 spreadsheets, one listing a list of sales, the other >> >> > showing a >> >> > summary of sales by each month. how do i get the summary from the >> >> > detailed >> >> > list by month. David One other question. What If I wanted to rerieve data from another worksheet what would be needed. thanks. john > I have now sorted it out. > Thanks very much foe all your help. [quoted text clipped - 31 lines] > > >> > detailed > > >> > list by month. Change =SUMPRODUCT(--(MONTH(A2:A100)=6),B2:B100) to =SUMPRODUCT(--(MONTH('Sheet 2'!A2:A100)=6),'Sheet 2'!B2:B100) if you want to retrieve data from a sheet called Sheet 2. SignatureDavid Biddulph > David > One other question. What If I wanted to rerieve data from another > worksheet > what would be needed. >> I have now sorted it out. >> Thanks very much foe all your help. >> > If you don't want to stretch the clairvoyant powers of the group, it >> > might [quoted text clipped - 7 lines] >> > your >> > system? >> > > Hi Toppers >> > > Thanks a lot but is the syntax correct as I keep getting formula [quoted text clipped - 22 lines] >> > >> > detailed >> > >> > list by month. |
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.