Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    sumproduct (excel2003)

    any explination why SUMPRODUCT function sum=61 days insteade of 212 days in the attched file.
    TIA
    dubdub

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: sumproduct (excel2003)

    Hi Dubdub

    It appears the formatting of your dates in column D are set wrong as they are currently set to 2065. I placed 1/3/1965 in all of them except D9 and I got a count of 212.
    Jerry

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: sumproduct (excel2003)

    The dates in column D are all text, not dates. Hence you are getting an alphanumeric comparison and 01-FEB-65 is greater than 01-DEC-03 because D is before F in the alphabet. If you adjust your formula to <code>=SUMPRODUCT((A2:A10=L3)*(B2:B10=M3)*(DATEVAL UE(D210)<DATEVALUE(O3)),E2:E10)</code> it should work.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: sumproduct (excel2003)

    thanks rory.
    TIA
    dubdub

  5. #5
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: sumproduct (excel2003)

    thanks Jezza.
    TIA
    dubdub

Posting Permissions

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