PDA

View Full Version : Solved: Braindead on Sumproduct

Opv
03-13-2010, 08:11 PM
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
03-13-2010, 08:36 PM
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
03-14-2010, 03:58 AM
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
03-14-2010, 06:23 AM
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
03-14-2010, 10:57 AM
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
03-14-2010, 02:25 PM
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