DSUM Not Very Convenient

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

archsmooth - 17 Sep 2007 18:10 GMT

I have a large worksheet named "By Week" with H columns of data, and about
6000 rows of data. I am trying to sum a range in a column labeled "Hours"
based upon a criteria in another column. In this particular case it is easy
because the column is "Weeks" and is numbered 1-53. On another worksheet I
labeled a row "Hours" followed by 1-53 and used: =DSUM('By
Week'!$A$1:$H$5903,"Hours",A$3:A4) to get the total Hours of data with 1 in
the Week criteria column. To get Week 2 hours I had to subtract another DSUM
as follows: =DSUM('By Week'!$A$1:$H$5903,"Hours",A$3:A5)-DSUM('By
Week'!$A$1:$H$5903,"Hours",A$3:A4), because A3:A5 would give me Week 1 plus
Week 2 hours. Note that only the last number in both the DSUMs does not have
$, so I could then drag the formula down and basically subtract out the
running totals to get the desired week.
Going forward will not be so easy since other column criteria are names and
other data, and subtracting DSUMs will not work unless I list every value of
the criteria column I am using. I want to be able to write a formula where I
can 'click' on the criteria value I want and not have a data range that
includes multiple values.

Reply to this Message

Peo Sjoblom - 17 Sep 2007 18:46 GMT

=SUMIF(Range1,1,Range2)

will sum Range2 where Range1 equals 1

=SUMPRODUCT(--(Range1=1),--(Range3=2),Range2)

will sum Range2 where Range1 equals 1 AND Range3 equals 2

so sumproduct is much more flexible for this kind of work than DSUM ever
will be.Maybe you should post back without involving DSUM and just inform
what you are trying to do

Signature

Regards,

Peo Sjoblom

>I have a large worksheet named "By Week" with H columns of data, and about
> 6000 rows of data. I am trying to sum a range in a column labeled "Hours"
[quoted text clipped - 21 lines]
> can 'click' on the criteria value I want and not have a data range that
> includes multiple values.

Reply to this Message

archsmooth - 17 Sep 2007 19:20 GMT

Actually, I had tried SUMIF, but was doing it wrong. I am too lazy to
actually type in the conditional each time, and was incorrectly using an '='
with the cell I selected that contained the criterion value I wanted. After
reading your reply, I tried again and found that a formula such as:
=SUMIF('By Week'!A$2:A$5903,'By Week'!A44,'By Week'!G$2:G$5903) where A44
contains the desired criterion will work nicely for all columns.

> =SUMIF(Range1,1,Range2)
>
[quoted text clipped - 33 lines]
> > can 'click' on the criteria value I want and not have a data range that
> > includes multiple values.

Reply to this Message