SUMIF function formula won't work, help

[ed]

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)*0.36+SUMIF($B:
$B,"AND(<9/1/2005,>12/31/2003),D)*0.375+SUMIF($B:
$B,"AND(<1/1/2006,>9/31/2005),D)*0.485+SUMIF($B:
$B,"AND(<2/1/2007,>12/31/2005),D)*0.445+SUMIF($B:
$B,">2/1/2007",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

 

[JMB]

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)-SUMIF(B:B, ">="&DATE(2005, 9, 1),
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]

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)-SUMIF(B:B, ">="&DATE(2005, 9, 1),
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.

 

[JMB]

Glad you got it working.

One last caveat, expressing dates in your formula like "<5/01/2006" or
--("5/01/2006") can cause problems if your spreadsheet is used on a machine
that does not have the same short date format set in Windows Regional
Settings (in control panel). U.S. settings interpret the above as May 1,
2006, while many European settings would interpret it as Jan 5, 2006. Also,
the short date format could be customized from one user to the next - so even
if the user has U.S. settings, the short date format could still be something
other than what you expect. The date function doesn't have this issue, but
you can decide for yourself based on who the intended users will be.

 

[ed]

I decided due to rated changes in the future to use your SUMPRODUCT
suggestion and have the following formula where column B contains
inputted dates, column D is inputted miles. Column N is dates and
column P is rates per mile per following chart at N4 to P8. All dates
inputted and in the chart are formatted as 3/4/2001 format everything
is on the same sheet.The formula is "parked" in column A and when
needed it can be copied below the last entry on any column. For other
sheets only the dates in column N and value is columln P need to be
changed, and when rates are changed in the future an entry can be
added to the bottom of the chart in N and P.

I am geting #N/A error. It worked when I first constructed it but it
was reading the wrong amount due to some typo errors and I moved the
chart from wheree ai originally constructed it to the N/P location at
row 4. What have I done wrong?

=SUMPRODUCT(LOOKUP($B$4:$B21,$N$4:$N21,$P$4:$P21),$D$4:$D21)

N P
1/1/2003 0.360
1/1/2004 0.375
9/1/2005 0.485
1/1/2006 0.445
2/1/2007 0.485

 

[JMB]

Possible causes could be a formula returning #NA somewhere in your data, but
more likely some of your cells in column B are empty, which would cause
Lookup to return #NA. Lookup returns the largest item in the table that is
smaller than what is trying to look up. For empty cells, it is trying to
look 0 up in the table, but the first entry is larger than the value being
looked up. You could add a 0 entry to the table:

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

or you could modify the formula to:
=SUMPRODUCT(IF(B4:B21<>"",LOOKUP($B$4:$B21,$N$4:$N21,$P$4:$P21),0),$D$4:$D21)

but it would need to be array entered with Cntrl+Shift+Enter.

 

[ed]

Well, that solved it, almost. 1/0/1900 returns 1, which is still
greater than the 0 assumed for a blank cell, so I changed the date to
0 instead of 1/0/1900 and it now works.

thank you a bunch. ed