Lookup and count all cells that fall between 2 dates

[Jonno]

Hi all,

I have a spreadsheet laid out as follows:

Date Cost
01/01/2011 10:03 5
01/01/2011 10:34 500
01/01/2011 14:18 10
01/01/2011 16:52 50
01/01/2011 20:59 2
02/01/2011 01:43 60
02/01/2011 21:16 50
03/01/2011 03:47 10
03/01/2011 13:17 7
03/01/2011 13:59 9
04/01/2011 10:21 17
04/01/2011 11:12 19
....and so on..

I want to count all the dates between a specific range but can't get a formula to work - I think it may be the times that are complicating matters!! Any help appreciated..

As an extension of this problem I then want to lookup the date range and then sum all the values in the cost column for the applicable dates.

Thanks in advance!

Jonno

 

[Pete_UK]

Assume dates in column A and costs in column B, with a start date in
C2 and finish date in D2, thenyou can use this to count them:

=SUMPRODUCT((A2:A100>=C2)*(A2:A100<D2+1))

and this to sum them:

=SUMPRODUCT((A2:A100>=C2)*(A2:A100<D2+1),B2:B100)

The +1 ensures that you include times up to 23:59:59 added on to your
finish date. I've assumed you have data up to row 100, so just make
this bigger if you have more data (must be the same for all ranges).

Hope this helps.

Pete

 

[Jonno]

Assume dates in column A and costs in column B, with a start date in
C2 and finish date in D2, thenyou can use this to count them:

=SUMPRODUCT((A2:A100>=C2)*(A2:A100<D2+1))

and this to sum them:

=SUMPRODUCT((A2:A100>=C2)*(A2:A100<D2+1),B2:B100)

The +1 ensures that you include times up to 23:59:59 added on to your
finish date. I've assumed you have data up to row 100, so just make
this bigger if you have more data (must be the same for all ranges).

Hope this helps.

Pete

Pete, that works an absolute treat! Thank you so much for your time.

Jonno

 

[Pete_UK]

You're welcome, Jonno - thanks for feeding back.

Pete