Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Array Sum with bad data (Excel 2000)

    I have a formular as: ={sum(A1:A10*B1:B10)}. If all data in A1:A10 and B1:B10 are numbers, it works well. However, if one cell has a non-numerical value, the formular fails to work. Any way to get around this ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Array Sum with bad data (Excel 2000)

    Use the "normal" (i.e. non-array) formula =SUMPRODUCT(A1:A10,B1:B10)

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Sum with bad data (Excel 2000)

    Hans,

    THansk. But if one cell contains non-numerical value, such as an "a", then the formula won't work. Or did I do something wrong ?

    TQ

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Array Sum with bad data (Excel 2000)

    If one of the cells contains a non-numerical value, SUMPRODUCT will ignore it, i.e. the cell will not contribute to the result. This is different from the array formula you had, which will return #VALUE in that case.

    What result do you get?

  5. #5
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Sum with bad data (Excel 2000)

    Hans,

    It worked, thanks. I used sumproduct(A1:A10*B1:B10), which explains why it did not work. Should not use "*" there.

    Regards.
    TQ

Posting Permissions

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