 |
 | 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 / April 2008DSUM function
Does anyone know anything about this function? I have been everywhere. I can not get it to work. I have tried every formula I can figure out. I even went to the library for the Excel 2003 for Dummies book and they dont even talk about this function. This is part of my homework and if I could just get this figured out without getting #VALUE error or just a 0 or more than what the total should be I would be home free and be able to finish this class. Thanks.  SignatureToo old to be in college I don't profess to know anything else than what I read in Excel help on DSUM so may not be able to assist much. What part of the Function is giving you the problem? Have you tried the examples from Excel help on DSUM? How do your answers differ from those in help on DSUM? Gord Dibben MS Excel MVP >Does anyone know anything about this function? I have been everywhere. I >can not get it to work. I have tried every formula I can figure out. I even [quoted text clipped - 4 lines] > >Thanks. I am doing this for school and the example they give in book does not help and I have been to every site there is plus books looking for help. Book says: In the range E4:E7, use the DSUM function to calculate the total number of vacation days remaining. This is my Employee Data worksheet A5 B5 C5 D5 E5 F5 G5 H5 I5 Name Status Yrs Empl Vaca Used vac Remaining Family Used Fam Rema leave leave vac leave Leave Leave Fam L Abba FT 0.1 7 5 2 3 0 3 and so on down the list of 109 names My Leave Summary worksheet: B3 C3 D3 E3 Vacat Leave Number Eligible Total Days Days Remaining 17-Day 36 612 12-day 29 348 7 day 6 42 none 31 0 Total 102 I tried this formula and I get 698 and the total should be like 456 =DSUM('Employee Data'!A5:I109,"Remaining Vacation Leave",D3:E7) I have tried others doing B3:E7 for criteria and get 0 for the total. You have to do this for each vacation leave day. I cant get it to give me the right answer for the first one let alone any of the others. Thank you in advance if you have any ideas SignatureToo old to be in college > Does anyone know anything about this function? I have been everywhere. I > can not get it to work. I have tried every formula I can figure out. I even [quoted text clipped - 4 lines] > > Thanks. The news reader has messed up your data layout considerably so can't tell what data is in which columns. You can post the workbook at one of the hosting sites below. http://www.freefilehosting.net/ http://savefile.com/ When you have uploaded to that site, post the URL so we could download should we choose. Gord >I am doing this for school and the example they give in book does not help >and I have been to every site there is plus books looking for help. [quoted text clipped - 29 lines] >right answer for the first one let alone any of the others. >Thank you in advance if you have any ideas [URL="http://www.freefilehosting.net/files/3fl1j"]Leave2.xls[/URL] SignatureToo old to be in college > The news reader has messed up your data layout considerably so can't tell what > data is in which columns. [quoted text clipped - 42 lines] > >right answer for the first one let alone any of the others. > >Thank you in advance if you have any ideas The D functions are rather ______ and you'll see why in a second..... I gave your data on sheet Employee Data A5:I107 the defined name of Table. I entered this data on sheet Leave Summary as the criteria range to be used by the DSUM function: Leave Summary: B11:D11 = Vacation Leave, Vacation Leave, Vacation Leave B12:D12 = 17, 12, 7 I entered this formula on sheet Leave Summary in cell E4 and copied down to E6: =DSUM(Table,6,INDEX(B$11:D$12,,ROWS(E$4:E4))) These are the correct results: 17 - 456 12 - 238 7 - 23 SignatureBiff Microsoft Excel MVP > [URL="http://www.freefilehosting.net/files/3fl1j"]Leave2.xls[/URL] > [quoted text clipped - 55 lines] >> >right answer for the first one let alone any of the others. >> >Thank you in advance if you have any ideas thank you very much. Yes these are a _____. I could not find anything anywhere explaining them well enough. Our book has one example and does not go into any detail about it. thanks again. KIM SignatureToo old to be in college > The D functions are rather ______ and you'll see why in a second..... > [quoted text clipped - 78 lines] > >> >right answer for the first one let alone any of the others. > >> >Thank you in advance if you have any ideas You're welcome! SignatureBiff Microsoft Excel MVP > thank you very much. Yes these are a _____. I could not find anything > anywhere explaining them well enough. Our book has one example and does [quoted text clipped - 92 lines] >> >> >right answer for the first one let alone any of the others. >> >> >Thank you in advance if you have any ideas That worked and I am done with the class. Thank you SignatureToo old to be in college > You're welcome! > [quoted text clipped - 94 lines] > >> >> >right answer for the first one let alone any of the others. > >> >> >Thank you in advance if you have any ideas In the real world, that could've been done with a very simple SUMIF formula. SignatureBiff Microsoft Excel MVP > That worked and I am done with the class. Thank you > [quoted text clipped - 107 lines] >> >> >> >right answer for the first one let alone any of the others. >> >> >> >Thank you in advance if you have any ideas Thanks from me also for your help on this Biff. I was as lost as Kimmer on DSUM Gord >You're welcome! I forgot to thank you. SignatureToo old to be in college > The news reader has messed up your data layout considerably so can't tell what > data is in which columns. [quoted text clipped - 42 lines] > >right answer for the first one let alone any of the others. > >Thank you in advance if you have any ideas The DSUM structure is: =DSUM(table_location,column_to_sum,criteria_range) The first argument in your DSUM formula should give the location of the entire table where the data is stored, not just the column you're trying to sum. If the table is on the Employee Data sheet, starts in cell A6 and ends in cell E109, the table's range would be: 'Employee Data'!A6:E109 The second argument in the DSUM formula should give the heading or number of the column that you want to sum. Since you want to sum the amounts in column E, you could enter a 5 for that part of the formula. The third argument gives the location where you have entered a heading and the criteria for which rows to sum. Perhaps column C has the heading Vac Days, and you want to sum the records which have 17 vacation days. To create a criteria range, go to cell D3 on the Employee Data sheet, and type Vac Days. In cell D4, type the number of days vacation: 17 In your DSUM formula, you'll refer to cells D3 and D4, which is the criteria range. Your revised formula would be: =DSUM('Employee Data'!$A$6:$E$109,5,'Employee Data'!D3:D4) > Does anyone know anything about this function? I have been everywhere. I > can not get it to work. I have tried every formula I can figure out. I even [quoted text clipped - 4 lines] > > Thanks. SignatureDebra Dalgleish Contextures http://www.contextures.com/tiptech.html I tried that and got a #NAME error. I sent another response to the guy who answered me before with more detail in it. Maybe if you look at that post it will help you help me. thank you in advance. SignatureToo old to be in college > The DSUM structure is: > =DSUM(table_location,column_to_sum,criteria_range) [quoted text clipped - 29 lines] > > > > 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.