Consulting

Results 1 to 6 of 6

Thread: Solved: Braindead on Sumproduct

  1. #1
    VBAX Expert
    Joined
    Feb 2010
    Posts
    689
    Location

    Solved: Braindead on Sumproduct

    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

  2. #2
    VBAX Expert
    Joined
    Feb 2010
    Posts
    689
    Location
    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.

  3. #3
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Syntax problem.
    Test problem code with a simple Messagebox

    [VBA]
    MsgBox date(year($a$4),month(1),day(1))

    MsgBox DateSerial(Cells(4, 1), 1, 1)

    [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  4. #4
    VBAX Expert
    Joined
    Feb 2010
    Posts
    689
    Location
    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

  5. #5
    VBAX Expert
    Joined
    Feb 2010
    Posts
    689
    Location
    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
    Last edited by Opv; 03-14-2010 at 11:52 AM.

  6. #6
    VBAX Expert
    Joined
    Feb 2010
    Posts
    689
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •