Help with complicated calculation
 

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

Sto 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!

--
Message posted via http://www.officekb.com

Help with complicated calculation
 

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

"cnagel via OfficeKB.com" <u11455@uwe wrote in message
news:6e7f4d5422d74@uwe...
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

Sto 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!

--
Message posted via http://www.officekb.com