sumproduct help using dates as the criteria
 

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.

sumproduct help using dates as the criteria
 

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

"Joe Peldoni" wrote:

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.

sumproduct help using dates as the criteria
 

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


--
__________________________________
HTH

Bob

"Joe Peldoni" wrote in message
...
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.

sumproduct help using dates as the criteria
 

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

"Mike H" wrote:

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

"Joe Peldoni" wrote:

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.

sumproduct help using dates as the criteria
 

Try my suggestion, it doesn't suffer this problem

--
__________________________________
HTH

Bob

"Joe Peldoni" wrote in message
...
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

"Mike H" wrote:

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

"Joe Peldoni" wrote:

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.