For one thing, 1/1/04 is interpreted as 1 divided by 1 divided by 4. Need to
use DATE(2004, 1, 1) or put the dates in a cell and reference those cells in
your formula.

Sumif doesn't work in the manner you are attempting. To use sumif to get a
sum between two dates, for example:
SUMIF($B:$B,""&DATE(2003, 12, 31), D:D)-SUMIF(B:B, "="&DATE(2005, 9, 1),
D:D)*0.375

or you could use sumproduct instead of 2 Sumifs to get data between two dates.

However, I would set up a table with mileage rates and the effective dates.
Let's say this table is on Sheet2!A1:B5:

0 0.360
1/1/2004 0.375
9/1/2005 0.485
1/1/2006 0.445
2/1/2007 0.485

Then I think this will work for you:
=SUMPRODUCT(LOOKUP(B1:B5,Sheet2!A1:A5,Sheet2!B1:B5 ),C1:C5)

assuming your dates are in B1:B5 and miles are in C1:C5. I only did limited
testing, so make sure to test it out for yourself.


"ed" wrote:

Column B is a list of dates, usually in ascending date order, and
column D are number of miles driven on each date to be reimbursed at
different $ per mile rates. Other columns have hours on various dates
to be reimibursed at different pay scales, etc. I'm looking for a
universal formula format I can apply to each type of reimbursement.
For instance I want to convert the total miles driven into dollars,
however, the reimbursement rate per mile has changed several times in
the past. I constructed the following formula. =SUMIF($B:
$B,"<1/1/2004",D:D)*0.36+SUMIF($B:
$B,"AND(<9/1/2005,12/31/2003),D:D)*0.375+SUMIF($B:
$B,"AND(<1/1/2006,9/31/2005),D:D)*0.485+SUMIF($B:
$B,"AND(<2/1/2007,12/31/2005),D:D)*0.445+SUMIF($B:
$B,"2/1/2007",D:D)*0.485.

The trouble is that the formula doesn't "pick up" values for dates in
the AND time frames. A date in the first and last time periods
(without the AND)records O.K.. Any suggestions?

TIA ed