dsum

[Stolie]

Here is the setup. I have three columns (Month, Day, and Number Collected)
that I am trying to obtain weekly totals for by using the dsum function
=DSUM(A1:C65,3,H1:I2). My criteria appear as follows: H1-Month I1-Day

H2-March I2- ?
I2 is were I am having trouble. If I want the formula to return the Number
Collected only for March 3 through March 7 and not for collections before or
after this week in March, how do I write that? I've been attempting to
combine >=3 and <=7 into the cell, but I'm not sure if my "language" is
correct. I appreciate any help. Thanks.

Stolie

 

[T. Valko]

Add another Day criteria:

..........H...........I...........J...
1....Month.....Day.....Day
2....March.....>=3.....<=7

=DSUM(A1:C65,3,H1:J2)

I find the D functions extremely convoluted to use and now days are
basically obsolete.

Much easier to use SUMPRODUCT although the D functions are more efficient on
*large amounts* of data. SUMPRODUCT is more flexibile (IMO).

=SUMPRODUCT(--(A2:A65="March"),--(B2:B65>=3),--(B2:B65<=7),C2:C65)

Or, use cells to hold the criteria (sort of like DSUM):

H2 = March
I2 = 3
J2 = 7

=SUMPRODUCT(--(A2:A65=H2),--(B2:B65>=I2),--(B2:B65<=J2),C2:C65)

 

[Stolie]

Can't believe I didn't think of that. Thanks for the added suggestion as
well, I think I'll set them both up and see which one I prefer. Your help is
apprecieated. Cheers. Stolie

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Herbert Seidenberg]

Same as Biff's plus 7 more options:
http://www.freefilehosting.net/download/3e7fe