Braindead on Sumproduct

[Opv]

Why is the following formula receiving an error?

=

SUMPRODUCT(--('Spread Sheet'!$A$5:$A$5000 >=date(year($a$4),month(1),day(1)),--($A$5:$A$5000<=$A$4),'Spread Sheet'!$L$5:$L$5000)

For clarification, the target date range and the target data to be summed reside in the sheet entitled "Spread Sheet' whereas the conditional date is in the actual sheet where the formula resides.

Thanks,

Opv

[Opv]

I should note that the formula works when I forego spelling out the date manually in the first criteria and instead referencing a cell with the same date in it, i.e.:

=SUMPRODUCT(--('Spread Sheet'!$A$5:$A$5000  >=$a$3),--($A$5:$A$5000<=$A$4),'Spread  Sheet'!$L$5:$L$5000)

This works. When I substitute the date as in the original post, I get an "Your formula contains an error" message.

[mdmackillop]

Syntax problem.
Test problem code with a simple Messagebox

MsgBox date(year($a$4),month(1),day(1))
MsgBox DateSerial(Cells(4, 1), 1, 1)

[Opv]

Thanks. I found the problem this morning when I looked at the formula with fresh eyes. I was missing a right parenthesis after the date portion of the formula. I stared at it for hours yesterday until the whole formula was blurry. It should have been:

=SUMPRODUCT(--('Spread Sheet'!$A$5:$A$5000  >=date(year($a$4),month(1),day(1))),--($A$5:$A$5000<=$A$4),'Spread  Sheet'!$L$5:$L$5000)

instead of

=SUMPRODUCT(--('Spread Sheet'!$A$5:$A$5000  >=date(year($a$4),month(1),day(1)),--($A$5:$A$5000<=$A$4),'Spread  Sheet'!$L$5:$L$5000)

Thanks again!

Opv

[Opv]

Well, I spoke a bit too soon. While I no longer receive an error and the formula produces a figure in the cell, the calculation appears to be incorrect. Please take a look at the attached sample sheet and advise what I've done wrong. My objective in the current formula is to sum the totals for the columns provided for the rows between (and including) the dates January 1, 2009 through December 31, 2009.

Thanks,

Opv

[Opv]

Well, I don't know what I did other than recreate the same identical formula, and now it's calculating correctly. I think I may have deleted a column after creating the original formulas and, while the cell references changed to the new ranges as expected, something happened to the way the formula was calculated. All I did was recreate the formula from scratch and it produced the correct results. Perhaps someone can expound on why this phenomenon occurs with SUMPRODUCT and not SUM, COUNT, etc.

At any rate, my problem is solved.

Thanks!

Opv