Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DSUM doesn't work (Excel 2000/2003)

    I need to pick out a date in column A and in the column next to it, or further right, (potentially 100 columns)sum the contents (hours and mins) of the column from that point down. DSUM seemed ideal but does not work. It just gives a figure for the cell opposite the selected date.

    This is the formula I am using; =DSUM($A$5:$U$68,"SMITH",$A$2:$A$3), where a5:u68 contains the figures I want to sum, Smith the column and a2:a3, Date and the date I want to total from.

    Any top tips gratefully accepted.

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

    Re: DSUM doesn't work (Excel 2000/2003)

    If you want to total data from a certain date on, do the following:
    - Enter the date in (say) C1.
    - In the criteria cell A3, enter a formula:
    <pre>=">="&C1
    </pre>

    If you're summing time values, don't forget to format the result as <!t>[h]<!/t>:mm or <!t>[hh]<!/t>:mm. The square brackets indicate accumulated time instead of clock time, allowing for hours over 24.

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSUM doesn't work (Excel 2000/2003)

    Hello, Hans

    I have enclosed an example for clarity. I have tried various combinations of reference but am struggling. I feel as if it's almost there and that I must be missing something obvious.

    Best regards,
    Farside

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

    Re: DSUM doesn't work (Excel 2000/2003)

    If you want to use DSUM, you must use field names in the first row of the table, and specify the appropriate field name(s) in the first row of the criteria range. Does the attached version do what you want?

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSUM doesn't work (Excel 2000/2003)

    Hans,

    That's exactly what I want to do! As you have pointed out, it was the full field name that was missing. Doh!

    Thank you very much!

    Best wishes

    Farside

Posting Permissions

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