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.