Using Sumif to Pull in Rates

[JohnDear]

A few mths ago we I started a project spreadsheet in which one column contains the Contractor’s rates. At the end/bottom of the sheet is a table that lists all contractors’ and rates, which is where the rates are pulled from.

My predicament is that within the next few wks some rates are increasing. If we continue to use the current formula =SUMIF($C$15:$C$20,$C6,$D$15:$D$20) it will change all previous rates referenced to a certain contractor.

Is there a way that this formula can be enhanced so when a rate changes it will not interfere with previous project rates?

Small sample attached; working in Excel 2010

[Bob Phillips]

If you left the old rates and date applicable in the rates table it would be doable.

[JohnDear]

Bob, not sure I follow. Can you please elaborate a little. Only a few, maybe even one rate may change. Do you mean add a column for effective date of rate and then add a if statement in formula. How would that work. Sorry, not advance in formulas.

[Bob Phillips]

What I mean is to save each rate when it changes with its effective date, therey having multiple rates in the rates table. Then it would be a simple formula to calculate based upon date, such as this array formula

=INDEX(E15:E21,MIN(IF((C15:C21=C5)*(D15:D21<=B5),ROW(C15:C21)))-MIN(ROW(C15:C21))+1)

[JohnDear]

Thanks for your help.