DSUM doesn’t work (Excel 2000/2003)

Topic

New Reply

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.

Reply | Quote

Replies

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:

=">="&C1

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote