Help with complicated calculation

[cnagel via OfficeKB.com]

Hello,

Need help figuring out a large amount of data. Here are the particulars. I
have a large worksheet of data. Data is sorted by location, then event date,
then total hrs. I need to calculate total hours used within each week for the
entire year, by location. the data looks like this...

This is what I need to populate:
A B C D E G

Store: 1 2
1 4/4/2005 4/10/2005 Sum>>> ???
2 4/11/2005 4/17/2005
3 4/18/2005 4/24/2005
4 4/25/2005 5/1/2005


This is what the data looks like... I want the formula to look up the Date
column (B-below) to see if in between the dates above (B & C-above) if yes
SUM Total Hours (C-below) within those 2 dates in column F-above.
A B C
Store Date Total Hours
2 4/1/2006 4.5
2 4/1/2006 4.5
3 4/1/2006 4.5
5 4/1/2006 4.5
5 4/1/2006 4.5
5 4/1/2006 4.5
6 4/1/2006 4.5
6 4/1/2006 4.5
7 4/1/2006 4.5
11 4/1/2006 4.5
15 4/1/2006 4.5
19 4/1/2006 4.5
19 4/1/2006 4.5
20 4/1/2006 4.5
22 4/1/2006 4.5
26 4/1/2006 4.5
26 4/1/2006 4.5

It is pretty complicated. Any ideas on whether Excel can do that? Any help
would be great!

 

[Don Guillett]

try these ideas
This will count for the year
=SUMPRODUCT((YEAR(a2:a22)=2007)*1)
or sum col B for the year
=SUMPRODUCT((YEAR(a2:a22)=2007)*b2:b22)

or for loacation 2
=SUMPRODUCT((a2:a22=2)*(YEAR(b2:b22)=2007)*c2:c22)


Don Guillett
SalesAid Software
(e-mail address removed)