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!

 

[Guest]

Hello,
I would try to start by using the subtotal function. At least this would get
you totals by day by store. Then from there I would select the timeframe I
needed and total in that manner.

First, sort your data by date and then by store.
Second go to "DATA" "Subtotal"
At each change in = Date
Use Function = SUM
Add Subtotal to = total hours
hit ok - this will give you the subtotals by date

Third, run another subtotal for the store
Go to "DATA" "SUBTOTAL"
At each change in "Store"
Add Subtotal to = total hours
*IMPORTANT* UN-CHECK REPLACE CURRENT SUBTOTALS

This will present your data in a more organized fashion. I am guessing you
will be able to select the date you need and paste the info on another
worksheet.

Hope this helps.

 

[Guest]

Something along these lines:

=SUMPRODUCT(($A$8:$A$24=A2)*($B$8:$B$24>=B2)*($B$8:$B$24<=C2)*($C$8:$C$24))

Copy down

A2=Store ... (OR are stores across columns ?)
B2=Start Date
C2=End Date


A8:A24=Stores
B8:B24=Date for comparison
C8:C24=List of hours

Hope this gives you an idea on how you might tackle the problem.