 |
 | 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 / New Users / November 2007Sumproduct?
Hello world, I am working with a xls log function in a filetransfer workflow. The worksheet is like this: Date/Time Job name Files in job 01-11-2007 Test01.jpg 1 02-11-2007 Test02.jpg 1 02-11-2007 Test03.jpg 1 03-11-2007 Test04.jpg 1 04-11-2007 Test05.jpg 1 My goal is to process this data in another worksheet. So far I have succeeded in having Excel adding the numbers in the "Files in job" column and displaying the sum in the second worksheet - yes, wow! ;-) Whats next is to have Excel add the amount of files transferred for a given timespan, display this in the second worksheet along with a graph illustration. Say I want the number of files transferred november or maybe the past week - is this possible? I have been on google for the past hours looking for a tutorial or something to help me construct this function but until now my desperate fiddle in Excel with sumproduct has been in vain.. Sincerly yours, Rasmus Olsen Denmark =SUMPRODUCT(--(YEAR(A2:A50)=2007),--(MONTH(A2:A50)=11),C2:C50) or something like this in Danish =SUMPRODUKT(--(ÅR(A2:A50)=2007);--(MÅNED(A2:A50)=11);C2:C50) Where A2:A50 holds the dates and C2:C50 the files in job for the past week use =SUMPRODUCT(--(A2:A50>=TODAY()-7),C2:C50) or in Danish =SUMPRODUKT(--(A2:A50>=IDAG()-7);C2:C50) the latter requires that your pc clock is up to date and I assume there cannot be any file transfers in the future  SignatureRegards/M.v.h. Peo Sjoblom > Hello world, > [quoted text clipped - 22 lines] > Rasmus Olsen > Denmark > =SUMPRODUCT(--(YEAR(A2:A50)=2007),--(MONTH(A2:A50)=11),C2:C50) > Where A2:A50 holds the dates and C2:C50 the files in job > for the past week use > =SUMPRODUCT(--(A2:A50>=TODAY()-7),C2:C50) Thank you for your reply Peo. If I copy/paste your form in my workbook I get the "The formulay you entered contains an error" dialogue. If I press ok for assistance in entering the form Excel places the cursor at the comma right after the '=2007)'. If I delete this comma the error dialogue returns and places the cursor at the next comma. When the last comma gets deleted Excel reports back "Excel found an error [...] Do you want to accept the correction proposed below? Press yes and Excel enters '=SUMPRODUCT(--(YEAR(A2:A50)=2007)-- (MONTH(A2:A50)=11)*C2:C50)' and I end up with the value '21' in the cell? >I assume there cannot be any file transfers in the future The workflow is running 24/7/365 so I need to have Excel "see" the entire column. BTW. I have uploaded my workbook to <ftp://freecall:dxpfree@ftp.dxp.dk/ excelfiddle/joblog.zip> (4K) in case you want to test. Sincerly yours, Rasmus On Nov 15, 10:06 am, r...@dxp.dk wrote: > BTW. I have uploaded my workbook to <ftp://freecall:dxpf...@ftp.dxp.dk/ > excelfiddle/joblog.zip> (4K) in case you want to test. ftp://freecall:dxpfree@ftp.dxp.dk/excelfiddle/joblog.zip (4k) On Nov 15, 10:25 am, r...@dxp.dk wrote: > ftp://freecall:dxpf...@ftp.dxp.dk/excelfiddle/joblog.zip (4k) My apologies, ftp.dxp.dk/excelfiddle/joblog.zip user: freecall pass: dxpfree November =SUMPRODUCT(--(MONTH(date_rng)=11),files_rng) last week =SUMPRODUCT(--(date_rng>TODAY()-7),--(date_rng<=TODAY()),files_rng) Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hello world, > [quoted text clipped - 22 lines] > Rasmus Olsen > Denmark > November > [quoted text clipped - 3 lines] > > =SUMPRODUCT(--(date_rng>TODAY()-7),--(date_rng<=TODAY()),files_rng) Mr. Phillips, thank you for your reply. As with Peos form suggestion it apperars that Excel wont accept the commas. Am I doing something wrong when entering these forms? Can you elaborate a little on how to set the ranges? Sincerly yours, Rasmus Try using semi-colons ( ; ) as you may have a continental edition of Excel. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >> November >> [quoted text clipped - 12 lines] > Sincerly yours, > Rasmus > Try using semi-colons ( ; ) as you may have a continental edition of Excel. Yup, that was it. Peos form works fine now but yours returns "#NAME" - I believe I have to define the range? Sincerly yours, Rasmus Olsen Yes, mine was generic, you need to substitute the actual ranges in there, or used named ranges. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >> Try using semi-colons ( ; ) as you may have a continental edition of >> Excel. [quoted text clipped - 4 lines] > Sincerly yours, > Rasmus Olsen > Yes, mine was generic, you need to substitute the actual ranges in there, or > used named ranges. Im a little thick - please bare with me ;-) Is it possible to have the form look at entire columns and not a defined range? Of course I can use A2:A1000 but there must be some kind of command for this.. Yours sincerly, Rasmus Olsen No you cannot. SP is a formula working on arrays, and arrays cannot include whole columns in pre-2007 Excel. You could use dynamic ranges OFFSET(A2,0,0,COUNTA($A:$A)-1),1) instead of A2:A1000, but you have to be careful that you base all ranges on the same count. For instance if you used A2:A1000 and B2:B100 you would use OFFSET(A2,0,0,COUNTA($A:$A)-1),1) and OFFSET(B2,0,0,COUNTA($A:$A)-1),1) note the count is based upon the same column. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >> Yes, mine was generic, you need to substitute the actual ranges in there, >> or [quoted text clipped - 6 lines] > Yours sincerly, > Rasmus Olsen > OFFSET(A2,0,0,COUNTA($A:$A)-1),1) > OFFSET(B2,0,0,COUNTA($A:$A)-1),1) Excel reports back an error. If I press ok it highlights the COUNTA part. A reference is displayed: =FORM(reference; rows; cols; [height]; [width]) - but even though I replace commas with semis and insert brackets I get the error message. A little help mr. Phillips ;-) Rasmus > OFFSET(A2,0,0,COUNTA($A:$A)-1),1) > OFFSET(B2,0,0,COUNTA($A:$A)-1),1) Excel reports back an error. If I press ok it highlights the COUNTA part. A reference is displayed: =FORM(reference; rows; cols; [height]; [width]) - but even though I replace commas with semis and insert brackets I get the error message. A little help mr. Phillips ;-) Rasmus > OFFSET(A2,0,0,COUNTA($A:$A)-1),1) > OFFSET(B2,0,0,COUNTA($A:$A)-1),1) Excel reports back an error. If I press ok it highlights the COUNTA part. A reference is displayed: =FORM(reference; rows; cols; [height]; [width]) - but even though I replace commas with semis and insert brackets I get the error message. A little help mr. Phillips ;-) Rasmus Sorry, I had one too many brackets in there OFFSET(A2,0,0,COUNTA($A:$A)-1,1) Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >> OFFSET(A2,0,0,COUNTA($A:$A)-1),1) >> OFFSET(B2,0,0,COUNTA($A:$A)-1),1) [quoted text clipped - 5 lines] > > Rasmus My Danish version used semicolon but you might have an English version with Danish regional settings SignatureRegards, Peo Sjoblom >> Try using semi-colons ( ; ) as you may have a continental edition of >> Excel. [quoted text clipped - 4 lines] > Sincerly yours, > Rasmus Olsen > My Danish version used semicolon but you might have an English version with > Danish > regional settings Hi Peo, a little help modifying your form to look at the entire A and C column? I have tried the A:A and C:C but receives #NUM. Sincerly yours, Rasmus > Sorry, I had one too many brackets in there > > OFFSET(A2,0,0,COUNTA($A:$A)-1,1) Hi Bob, it works now but returns a high value (39387) - could Excel be adding the dates to the calculation? Rasmus What is the whole formula now? Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >> Sorry, I had one too many brackets in there >> [quoted text clipped - 4 lines] > > Rasmus
|
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.