A better use of SUMIF???

[vzc6rp]

I have a spreadsheet I am summing values from one column based on the dates in another. It is working just fine, however, when I change the dates I have to manually modify my SUMIF forumla every time to perform the summing.

The data looks like this.

Column A Column B
7/4/2011 0:01 7
7/4/2011 0:16 10
7/4/2011 0:31 20
7/4/2011 0:46 22
7/4/2011 1:01 22
7/5/2011 0:01 15
7/5/2011 0:16 22
7/5/2011 0:31 24
7/5/2011 0:46 26
7/5/2011 1:01 27

The formula I am currently using is
=SUMIF(A:A,"<="&DATE(2011,7,5),B:B)-SUMIF(A:A,"<"&DATE(2011,7,4),B:B)
=SUMIF(A:A,"<="&DATE(2011,7,6),B:B)-SUMIF(A:A,"<"&DATE(2011,7,5),B:B)

The first SUMIF for 7/4/2011 would give me the total of 81 and the one for 7/5/2011 would be 114.

Like I said, this works but if I change the date values and I don't manually modify my formulas my totals are always 0.
Also I can not strip out the second part of that date range because I need to know which 15 min time block that count occurred in.

My question is how do I sum the values in column B based on the date in column A each week without having to modify the formulas for each date?

 

[jasonb75]

Try

=SUM(SUMIF(A:A,"7/4/2011"+ROWS($C$1:$C1)-{0,1},B:B))

Where C1 is the cell holding the first formula, then fill down.

 

[vzc6rp]

This did not work. I gives me a total of 0.

 

[ChrisM]

Why not just have your date criteria point to two cells in the worksheet, say, E1 and E2

E1 = 7/4/2011
E2 = 7/5/2011

=SUMIF(A:A,"<="& E2,B:B)-SUMIF(A:A,"<"& E1,B:B)

Then you can just change the values of those two cells each week.

 

[vzc6rp]

That works great, Thanks