Sum value between dates problem driving me mad!2

[Mathew P Bennett]

Good Evening All,
Many aplogies for the crappy formatting of my previous post, I have done it
properly (I hope) now.
I am really struggling here, have tried, nested IF's including AND's but am
a bit stumped.
I am fairly familiar with Arrays, but cannot seem to combine all.
I have a data table (as detailed below). I simply wish to calculate the
expected totals per month.
Any help & assistance with this would be most welcome.
Cheers,
Mathew

Note, earliest Start is 01/04/03
Start Finish DayRate Apr-2003
May-2003 Jun-2003 Jul-2003 Aug-2003
01/04/2003 23.00
01/04/2003 09/07/2003 23.00
01/04/2003 30/07/2003 23.00
01/04/2003 23.00
29/04/2003 23.00
01/04/2003 23.00
01/04/2003 23.00
01/04/2003 23.00
01/04/2003 23.00
01/04/2003 09/04/2003 23.00
01/04/2003 23.00
01/04/2003 23.00
30/04/2003 23.00
29/05/2003 23/06/2003 23.00
19/05/2003 02/06/2003 23.00
26/06/2003 23.00
22/07/2003 23.00
07/07/2003 21/07/2003 23.00
01/04/2003 14/04/2003 23.00
22/04/2003 24/04/2003 23.00
14/04/2003 28/04/2003 23.00
01/04/2003 23.00
01/04/2003 23.00
14/04/2003 19/04/2003 23.00
12/05/2003 02/06/2003 23.00
23/05/2003 08/06/2003 23.00
12/05/2003 26/05/2003 23.00
19/05/2003 26/05/2003 23.00
27/05/2003 24/06/2003 23.00
19/05/2003 27/05/2003 23.00
30/06/2003 21/07/2003 23.00
09/06/2003 23.00
02/06/2003 09/06/2003 23.00
31/07/2003 24/08/2003 23.00
21/07/2003 23.00
09/07/2003 14/07/2003 23.00
14/07/2003 21/07/2003 23.00
07/07/2003 21/07/2003 23.00
28/07/2003 11/08/2003 23.00


Finish

 

[Dan E]

Mathew,

Could you please explain further what it is your looking for?

Your looking for a sum between dates so i'm assuming that
you want your sum to add up column C (Day Rate) when
the ?Start Date? falls between April 1 and April 30. Or
did you want the sum based on the Finish Date?

Perhaps you could provide a sample output, just so that
we can verify our solutions work to your specifications.

Dan E

 

[Dan E]

Mathew,

0,(IF($B2:$B40<=(D$1+29), IF($B2:$B40<>"",$B2:$B40,D$1+29), D$1+29))-IF($A2:$A40>=D1,$A2:$A40,D1) + 1,0)*$C2:$C40)

Watch out for line wrapping, it's one gigantic formula.
Note: built this formula for april (note the 29's - one day
less than in the month) change accordingly for different
months.

Thought i'd explain somewhat,
I assumed that a blank finish date means the project is
ongoing. If a project spans two months it's broken up
(based on the # of days worked in each month)

Dan E

 

[Dan E]

Don't know how I forgot, but . . .

That formula has to be array entered (control + shift + enter)

Dan E