 |
 | 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 / January 2006SUMPRODUCT Help Please.....
I have read through the explantion of SUMPRODUCT here........ http://www.xldynamic.com/source/xld.SUMPRODUCT.html I can understand the reasoning behind the tables 3 4 5 and 6......but when it comes to actually using SUMPRODUCT for conditional calculations I just can't get it to work. How can I get it calculate say........... Column A = list of Dates Column B = List of companies. Each row in relation to the dates and contains two cells with monetary values i.e 200 Fee Using SUMPRODUCT How do I add the two cells monetary values for each company between and calculate he complete total of thee between a period of two dates? Any explanations or even a link to a downloadable example would be very much appreciated, as I have spent a lot of time trying to figure out how to reason this out. Thanks Hi! What does this mean: >Each row in relation to the dates and contains >two cells with monetary values i.e 200 Fee Biff >I have read through the explantion of SUMPRODUCT here........ > http://www.xldynamic.com/source/xld.SUMPRODUCT.html [quoted text clipped - 14 lines] > reason this out. > Thanks I think I can interpret it (but I'm not *absolutely* sure) ... If the dates are in ColA, and the Company names are in ColB, and Fee1 is in ColC, and Fee2 is in ColD, and a formula to add Fee1 + Fee2 is in ColE, and the data rows go from 2 to 100, then a formula like =SUMPRODUCT(--(StartDate<=A2:A100),--(EndDate>=A2:A100),--(CompanyNam e=B2:B100),--(E2:E100)) should do it. Note that StartDate, EndDate & CompanyName all indicate references to cells that contain your search parameters. Rgds, ScottO | Hi! | [quoted text clipped - 23 lines] | > reason this out. | > Thanks Hi Scotto, Thanks for the reply. Your understanding of my enquiry is correct except....... To quote you:- "and a formula to add Fee1 + Fee2 is in ColE" The spreadsheet I am using doesn't have a Fee 1 + Fee2 column Would I need to include one or can this calculation be included in the formula? > I think I can interpret it (but I'm not *absolutely* sure) ... > If the dates are in ColA, and the Company names are in ColB, and Fee1 [quoted text clipped - 43 lines] > | > reason this out. > | > Thanks Do it in the formula then =SUMPRODUCT(--(StartDate<=A2:A100),--(EndDate>=A2:A100), --(CompanyName=B2:B100),(C2:C100+D2:D100))  SignatureHTH Bob Phillips (remove nothere from email address if mailing direct) > Hi Scotto, > Thanks for the reply. [quoted text clipped - 52 lines] > > | > reason this out. > > | > Thanks Thanks Bob I have eventually got it to work.....you did mention to me in a previous posting to ensure I was using the correct date format........I thought I had understood ........but when I red Rogers posting below.......Data | Text to Columns etc......I wasn't although it appeared to be the correct format visually.....it was text instead of a date format I was using...... Many thanks for your help with this problem for me. Dermot Sorry about the thoughtless earlier posting. > Do it in the formula then > [quoted text clipped - 57 lines] > > > | > reason this out. > > > | > Thanks Thanks Scotto > I think I can interpret it (but I'm not *absolutely* sure) ... > If the dates are in ColA, and the Company names are in ColB, and Fee1 [quoted text clipped - 43 lines] > | > reason this out. > | > Thanks Hi Dermot I notice from previous postings you have made on this subject you mention your dates as 23.12.05 for example. If this is the case, then Sumproduct will not work as you expect because these are not valid Excel date formats. Change them to something like 23/12/05 and you should find it will work. The easy way to convert your entries would be to use Data>Text to Columns>Next>Next>choose Date DMY>Finish SignatureRegards Roger Govier >I have read through the explantion of SUMPRODUCT here........ > http://www.xldynamic.com/source/xld.SUMPRODUCT.html [quoted text clipped - 18 lines] > reason this out. > Thanks Thanks Roger I have struggled with this. I had used the format 23/12/05.......but I assume it was text and not a date format. Following your explanation Date | Text to Column etc.....and selecting date has resolved my problem. I am not sure how I managed to do this....as the method I used was.....selected ROW A | Format|Date|23/12/05...........should this have resulted in correct Excel date format? > Hi Dermot > [quoted text clipped - 28 lines] > > reason this out. > > Thanks Hi Dermot, I'm glad you have now got it resolved. Just for your information, with data in the column in the format 13.12.05, just selecting column A and Formatting as date DD/MM/YY format will not do anything. Formatting the column that way, then inputting a date value will cause the date value to be shown in that format. In your case, it did need to go through the conversion process first. SignatureRegards Roger Govier > Thanks Roger > I have struggled with this. [quoted text clipped - 47 lines] >> > reason this out. >> > Thanks Thanks again Roger Best wishes Dermot > Hi Dermot, > I'm glad you have now got it resolved. [quoted text clipped - 56 lines] > >> > reason this out. > >> > Thanks |
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.