DSUM Not Very Convenient

[archsmooth]

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.

 

[Peo Sjoblom]

=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


--


Regards,


Peo Sjoblom

 

[archsmooth]

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.