Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dsum from Lotus (Excel 2002 SP3)

    I have converted a spreadsheet from Lotus and have all of the formulas working except for a DSUM. I am very much a beginner with Dsum, and am wondering if anyone can tell me what is missing. A fee is supposed to go in column B and initials in column C. Based on the initials the value should end up in the correct row of column H. I have attached a sample. The conversion log indicated the following:

    <hr>$A:$H$7 Argument invalid. @Function: DSUM; Argument number: 3.
    $A:$H$7 Cannot translate formula.
    Formula value: 0<hr>
    Attached Files Attached Files
    egghead

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dsum from Lotus (Excel 2002 SP3)

    Can you explain more clearly and in detail what you want to accomplish?

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: Dsum from Lotus (Excel 2002 SP3)

    The attachment is general and it is hard to figure what results you want.
    DSUM needs 3 elements
    1. Database
    2. Fiield
    3. Criteria

    =Dsum(database,field,criteria)

    In your workbook from Lotus the database is not in the proper format.
    The proper format for the data is each column must have a unique label at the top
    The data must then begin directly below the top label. Your data does not begin just below the labels.

    Eaxample of valid database

    Name WFee MoFee YTD Fee
    abc 25 125 700
    def 15 75 400
    abc 50 375 1300

    The field is the cell reference of the column label you want to sum. In the above example "year to date fee" will sum the year to date fees for the criteria you want.

    The criteria must consist of the label and the element you want to get a result of.

    so in say cell A5 You would put "Name" and cell A6 would put "abc"

    Then Dsum(database,"YTD Fee",$A$:$A$6) would show the result 2000

    Hope this helps.

    Tom Duthie

  4. #4
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dsum from Lotus (Excel 2002 SP3)

    Sorry if I wasn't clear. The user is supposed to enter a list of fees and the initials of who earned them in the data entry area (A5:E41). There would be an "amount" like $100 in A7. The portion of the total amount that is a "fee", say $50, would go in B7. Then the attorney's initials would go in D7. That would continue down to row 41, as needed. There may be blank rows, a variable number of fees collected each month.

    In H6 there is a DSUM formula that came over from the Lotus conversion. =DSUM(A5:E41,4,L1:Q2). That's the one I'm referring to in my original post. This is what happens when this worksheet is completed in Lotus: When fees and initials are entered in the Lotus worksheet, the fee entered in A7 that I mentioned above, $50, displays in H6, and in the column of H7:H11 in the cell that corresponds to the matching initial in column G. The data entry in A741 can be done in any order, because the formula in H6 somehow knows where the matching initials are in column G. Also, if no initial is entered in Column D, the $50 fee from A7 would be allotted to each attorney's initials in G.

    In my attached Excel example, the initials in D8 are "LWK', but but the $50 was placed in the same row in H8, when it should have gone to H7, one row up. I don't get the result of $50 by each attorney's initials by omitting initials in Column D, either.

    The errors in column K are due to my eliminating some data from the worksheet when creating a small sample to upload. Sorry about that.
    egghead

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dsum from Lotus (Excel 2002 SP3)

    I don't understand the formulas and values in columns I through K, but I think you should use SUMIF in column H, not DSUM.
    In H7 enter the formula =SUMIF($D$6:$D$41,$G7,$B$6:$B$41)
    Fill down to H11.
    In H12, enter the formula =SUM($B$6:$B$41) to get the total.

  6. #6
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: Dsum from Lotus (Excel 2002 SP3)

    In workbook you attached you are missing a critical piece of informaiton in cell $P$2. This is the Criteria needed by both Lotus and Excel to determine what you want to Sum. If you leave this blank you will it will not return a result.

    The attached workbook on the second worksheet shows what DSUM is doing. The Yellow area is the Database, The Pink cell is the Field (the column of data to be summed) and the Green is the Criterea. It is very important that creteria consist of two elements. Element 1 is the identical label as the column label to be summed,. Element 2 is the item in the database you are trying to find, in you example It appears to the be initials of the Attorney. So in $P$2 I put the initials PJW, if you change that to LWK you get different results which is what I think you want.


    Regards,

    Tom Duthie
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •