formula help

[hawgrider1]

I am looking for a formula. I have individual dates (A) with multiple delivery sums (C) at multiple locations (B). I would like to get the sum of deliveries per location by week, i.e.  10/1-10/6.
10/17/2011 238 8706
10/17/2011 102-A 4404
10/17/2011 228 13302
10/24/2011 238 5818
10/24/2011 239-A 4706
10/24/2011 102-A 2904
10/25/2011 228 8700

 

[Andrew Poulsom]

Have you tried a pivot table? You can Group the Dates by days, with options for start date, end date and number of days.

 

[hawgrider1]

thanks for the response,
I am inserting it into another formula. What i am doing is i have a sheet that i enter data on weekly. its a fluid level, ie 4000 gallons. I then have the sheet subtract from the previous week giving me the usage for tht week. 4000 gal. reading on 10/1 minus 3500 gal. reading on 10/7 = 500 gallons used. the problem comes in when i have a delivery. now 10/7 =3500 gal. i get a delivery on 10/10 and 10/12 for 1000 gal. now my calculation is off.
10/1 reading=4000
10/7reading= 3500
usage= 500 gal

10/7reading=3500
10/10 1000 gal increase
10/12 1000 gal increase
10/14 reading= 5200
usage = -1700

i enter deliveries on a separate sheet. so i am looking for a formula that will add all the increase within that week. Hope this helps. I have learned excel by necessity not school. I took a local class and the teacher was intimidated by what i have trying to accomplish.

 

[Andrew Poulsom]

With this data in A1:C5:

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192 x:str><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 width=64 align=right x:num="40823">10/07</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=64>reading</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 width=64 align=right x:num><FORM id=inlinemodform method=post action=inlinemod.php?threadid=590174&p=>3500


</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="40826">10/10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>increase</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num>1000</TD></TR><FORM id=inlinemodform action="inlinemod.php?threadid=590174&p=" method="post">


<TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="40828">10/12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>increase</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num>1000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="40830">10/14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>reading</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num>5200</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>usage</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num x:fmla="=C4-SUM(C1,SUMPRODUCT(--(A2:A3>A1),--(A2:A3<A4),C2:C3))">-300</TD></TR>
</TBODY></TABLE>
the formula in C5 is:


=C4-SUM(C1,SUMPRODUCT(--(A2:A3>A1),--(A2:A3 < A4),C2:C3))<?XML:NAMESPACE PREFIX = A4),C2 /><A4),C2:C3))< p></FORM>
</A4),C2:C3))<>

 

[hawgrider1]

Thanks, That will get me started.