sumproduct date calc.

[Guest]

Hi I am trying to sum a column of numbers by month and am having trouble with
the formula (see below). I am getting a return of VALUE. Any ideas where I
am going wrong?

Thanks


Todd

=SUMPRODUCT((MONTH(J2)=MONTH($H2:$H$300)),F$2:F$300)

F H j2
cost date requested Month
$12.46 1/6/2006 1/00/2006
$200.70 1/19/2006
$53.22 1/6/2006
$471.84 1/7/2006
$25.35 2/9/2006
$33.00 2/11/2006
$346.98 2/14/2006
$3.82 2/15/2006
$99.42 2/8/2006

 

[Guest]

Try changing your formula to something more like this...

=SUMPRODUCT(--(MONTH(J2)=MONTH($H2:$H$300)), F$2:F$300)

and the value in J2 to a valid date (1/00/2006 is not a valid date)...

 

[Guest]

Thanks. I don't get it. I tried your adjustments and they don't work. I
have tried the formula in different columns and with different categories but
it does not work. I either get value or a return of zero. I have used the
formula successfully before but something on this sheet is not quite right.

Thanks.


Todd

 

[Guest]

Is the value in J2 an actual date or is it text? That could be the problem. I
used that exact formula successfully. It does require the double negative at
the front of the month criteria otherwise it will return 0.

 

[Guest]

1/00/2006 is not a valid date.
Try to change this 1/1/2006 it will work with your formula