Help on this Please . How do i find a count between two date ranges

[Rahul.N]

for ex:
i receive
3 Parcels on 28-Oct-2013
4 Parcels on 2 Nov-2013
8 Parcels on 15 th Nov -2013
7 Parcels on 20 th Nov -2013
20 Parcels on 16 th Nov -2013
12 Parcels on 12 Dec -2013
2 Parcels on 1 st Jan -2014
3 Parcels on 2nd Dec -2014 ( all these put together in a table )

Question here is i evaluvate the number parcels received in milestones.
Milestone 1 18th Nov
Milestone 22 nd Dec
Milestone 3 is 10 Feb

So, now my result will be like this
on or before milestone 1 date i am receiving 42 Parcels (on referring to the above data)
On or before Milestone 2 date i am receiving 15 Parcels + 42 Parcels (which is the total of milestone1) & so on as for the next milestone.

Please give me a formula for this which will be very helpful.

 

[NARAYANK991]

Hi Rahul ,

Can you explain how your data is laid out ?

What you have posted above , is it in one cell or is the number of parcels in one cell and the date in another cell ?

Similarly for the milestones , are the numbers and the dates in two separate cells ?

Narayan

 

[Rahul.N]

Narayan i have uploaded the workbook

 

[NARAYANK991]

Hi Rahul ,

Check your file now.

Narayan

 

[Rahul.N]

this is good but there is one error if suppose if i change the date of on item beyond the milestone 1 , it is getting reduced in the milestone 1 count but not getting added in the milestone 2 count .

 

[NARAYANK991]

Hi Rahul ,

Since I already have your workbook , can you mention exactly what you did , including the cell reference ?

I'll try it out in the workbook I have and get back to you.

Narayan

 

[Rahul.N]

Narayan please find the uploaded file i would expained what the error is

 

[NARAYANK991]

Hi Rahul ,

I don't see why anything should change in the count against the second milestone !

I will quote from your first post :

on or before milestone 1 date i am receiving 42 Parcels (on referring to the above data)
On or before Milestone 2 date i am receiving 15 Parcels + 42 Parcels (which is the total of milestone1) & so on as for the next milestone.

Your list has a total of 20 entries , all of which are prior to the date of the second milestone , so the count against the second milestone is 20 ; if you change any date in your list to a date prior to the date of the second milestone you will not observe any change anywhere in the output.

If you change a date which is prior to the first milestone , to a new date which is between the first and the second milestone dates , then the count against the first milestone date will reduce by 1 , but the count against the second milestone will remain 20.

Narayan

 

[Rahul.N]

oh yes yes !! i am sorry i was thinking the total as 21 , sorry i messed up. this great and superb. you guys are excellent.

 

[Sajan]

Hello Rahul,
Not sure if you wanted to change the date in cell C2 to a date in the year 2010 or 2013. The counts do not change when the date is in 2010 since that date is less than the date in F23

Cheers,
Sajan.