Calculating rates using sumproduct
 

Probably easy for you, but I'm not that good with sumproduct.

I have 3 Worksheets simplified below (some columns are not next to each
other, but spaced out, (* and % = colums next to each other)))

Rates,Hours and summary

I have 2 special periods : -1 and 13 :- period -1 is for before the FY
starts, and 13 is for after, so I can't search just on the month.

e.g. below, headings are the same

Rates
Period* | Month* | Start Rate | Changed Rate% | wef%
-1 | B4 FY | 10.00 |
1 | April | 10.00 | 12.00 | 15/04/07
2 | May | 12.00 | |
3 | June | 15.00 | 15.00 | 01/06/07
4 | July | 15.00 | |
...
12 | March | 23.00 | 25.00 | 28/03/08
13 | after FY | 25.00
etc.

The month starting rate, is autofilled from above, or changed rate
depending on if there was a change (if date of change = 1st, then
changed also)

Hours consists of:

Period*| DAte of Work* | Hours doing job
-1| 01/04/2007 | 10.00
1| 13/04/2007 | 12.00
8| 16/11/2007 | 5.00
13| 11/04/2008 | 20.00

now on the summary I have:

Period* | Month* |<OTHER COLUMS| Total Hours% | Cost%
1 | April | | ^^ |

^^{=SUM(IF(INDIRECT($D$1)=$D7,INDIRECT(O$1),0))}

taken out sheet references below:

Hidden at the top of the summary page, are formulae, to work out the
last
filled row in column A. :- =MATCH(-99999,A:A,-1)

D1 contains "A1:A99" where 99 = last row
O1 contains the range, for the total hours column

now what I would like, is a formulae, that calculates the total cost
for each period, taking into account any rate change that has happened
(i.e. hours*rate on a date). for Period -1, the starting rate at 1 is
assumed, likewise for period 13, the ending rate for 12 is assumed.

I guess sumproduct would be the best here, but I am unsure on how to
calculate it, and whether it would be entered as an array formula, or
just a normal one. The wef date is the 1st day the new cost starts.

---edit---

if it's easier, I can add an extra column at the end and do it on a
line by line basis.

---end edit---

Many Thanks in advance




--
masterkeys