Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: sumproduct function calculating wrong?

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Finland
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I create new sheet, format all cell to be number type. Then I paste data from another sheet, using paste special -> values
    A B
    15973,26 1,00
    318,10 18,00
    98,94 8,00
    251,09 1,00
    276,68 4,00
    column A has value, column B has multiplier for value. When I count A1*B1+A4*B4+A5*A6 I get 17331,07 which is correct. But when I use =SUMPRODUCT(A1:B5) I get value 16950,7 which is impossible value. Correct value would be 23848,30. Does somebody have clue where this goes wrong? Same error happens when using general type cells. Desimal
    separator is , (european style)

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    United Kingdom
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Based on the data you posted, the sums you are getting are correct.

    Your answer of 16950.07 is the sum of:
    A1+B1+A2+B2+A3+B3+A4+B4+A5+B5

    Nobby





  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,656
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-06 05:18, donalde wrote:
    I create new sheet, format all cell to be number type. Then I paste data from another sheet, using paste special -> values
    A B
    15973,26 1,00
    318,10 18,00
    98,94 8,00
    251,09 1,00
    276,68 4,00
    column A has value, column B has multiplier for value. When I count A1*B1+A4*B4+A5*A6 I get 17331,07 which is correct. But when I use =SUMPRODUCT(A1:B5) I get value 16950,7 which is impossible value. Correct value would be 23848,30. Does somebody have clue where this goes wrong? Same error happens when using general type cells. Desimal
    separator is , (european style)
    It's:

    =SUMPRODUCT((A1:A5)*(B1:B5))


  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Use...

    =SUMPRODUCT(A1:A5,B1:B5)

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Finland
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Many thank to all who replied, I have made stupid mistake on syntax. Mark W, thanks for giving correct syntax.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,656
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-06 22:53, donalde wrote:
    Many thank to all who replied, I have made stupid mistake on syntax. Mark W, thanks for giving correct syntax.
    =SUMPRODUCT((A1:A5)*(B1:B5))

    is also correct syntaxwise.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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