sumproduct help using dates as the criteria

[Joe Peldoni]

I'm having trouble figuring how to work with dates in one column as the
criteria to add amounts in another column.

The desired outcome is to add those amounts in column B that occur between
two dates (say, between 2/26/09 and 5/10/09). I've used the following formula
but get #VALUE for an answer:

=SUMPRODUCT(--(A16:A421>=1/1/09),--(A16:A421<2/1/09),--(I16:I421))

This is using Excel 2003.

 

[Mike H]

Joe,

Try it like this. This does Jan 2009

=SUMPRODUCT((A16:A421>=DATE(2009,1,1))*(A16:A421<=DATE(2009,1,31))*(I16:I421))

Mike

 

[Bob Phillips]

=SUMPRODUCT(--(A16:A421>=--"2009-01-01"),--(A16:A421<--"2009-02-01"),I16:I421)

 

[Joe Peldoni]

Mike,

Thank you for your expert advice in relation to my question. Fast and
accurate, great! I do have a followup, though. When I applied the formula to
my worksheet it returned a #VALUE error. This is because the cells in Column
I contain formulas.

I use the worksheet to do a check spread posting the check amounts to
expense columns based on a "code" entered elsewhere on the line. For example,
the expense in Col I is "Fuel" and the code I use for Fuel is "3". If the
check written is for fuel, then "3" is entered in Col F and the amount in Col
E is posted to Col I. If the code is some other number, then the formula puts
a blank (not a zero) in the particular cell number.

The formula you provided is used to total the expense for a particular range
of dates, say a month or a quarter. The result I get using the formula is the
#VALUE error message because of the presence of numerical data in the cell.
How do I get the desired results?

The formula in Column I is as follows:

=if($F42=3,$E42,"")

Thanks again, Mike

Joe

 

[Bob Phillips]

Try my suggestion, it doesn't suffer this problem