On Sep 21, 6:44 pm, JMB wrote:
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.

JMB: I ended up using your first suggestion for where the rate only chasnged once or twice, per the following:

=SUMIF($B:$B,"<1/01/2006",A:A)*90+SUMIF($B:$B,"12/31/2005",A:A)*92-
SUMIF($B:$B,"5/20/2007",A:A)*92+SUMIF($B:$B,"5/19/2007",A:A)*94

I "store" the formula in a column A cell and I just paste the formula
at the bottom of each column. Note that entering the dates "bare"
works fine. I had already built a table as per your final suggestion
(although it was siimpler that yours as I didn't use LOOKUP), for the
milage but will probalbly go back and create the formula above, but
with more dates.

Thanks for all the suggestions, however.

"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- Hide quoted text -

- Show quoted text -