Sum data between date range

[demelza]

Hi

I have 2 Columns -

A B
Date Hours <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
21/02/2011 8<o></o>
05/03/2011 9<o></o>
05/03/2011 12.5<o></o>
07/03/2011 3<o></o>
10/03/2011 21<o></o>
14/06/2011 26.75<o></o>
05/07/2011 1.5<o></o>
11/07/2011 2<o></o>
11/07/2011 3<o></o>
04/08/2011 17

There are thousands of rows of similar data. We work on a weekly finance system, and I need to sum hours burned within a particular week for every week going back a few years. I was thinking I could create column C ‘Start Date’ and column D ‘End Date’ and drag down my weekly dates, then write a formula that I can drag down in column E that translates to:<o></o>
<o> </o>
“Sum $B$1:$B$100 IF $A$1:$1$00 is >=C1 and/or <=D1”<o></o>
<o> </o>
For example if I had a start date of 05/03/2011 and end date of 11/03/2011, the answer would be ’45.5’.<o></o>
<o> </o>
I’ve tried SUMIF’s and SUMPRODUCT’s and DSUM’s and VLOOKUP’s, but I can’t get it to work, I'm obviously going wrong somewhere – I either get ‘0’ or #VALUE errors. Any suggestions or advice would be greatly appreciated.<o></o>
<o> </o>
Thanks in advance
<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on">Dee</st1lace>
<st1lace w:st="on"></st1lace>
<st1lace w:st="on">PS. I'm from New Zealand hence the different date format!</st1lace>

 

[Biz]

Try formula below

=SUMPRODUCT(--($A$2:$A$11>=$C$1),--($A$2:$A$11<=$D$1),--($B$2:$B$11))

Biz

 

[Biz]

Another alternatively solution is

=SUMPRODUCT(($A$2:$A$11>=VALUE($C$1))*($A$2:$A$11<=VALUE($D$1))*($B$2:$B$11))

Biz

 

[demelza]

Thanks for getting back to me Biz. I've tried both of the formula's however both times I just get '0'. I've tried committing both formula's with Ctrl Shft Enter and doesn't work either. The 3 date columns are formatted as Dates, and the Data column is General (I tried 'Number' but this didn't work either). Can you think where I am going wrong?

I've attached an HTML of a sample of the data - I've never done this before so hopefully it works.

Thanks again

Dee

 

[demelza]

http://<b>Excel 2003</b><table cell.../td></tr><tr ><td style="color: #161120;text-