Results 1 to 7 of 7

Thread: sumproduct

  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    In the attached file, I do not have to put quotes around the reference in column A, whereas in other files, when I don't do so, the formula returns an incorrect result. Any ideas?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The values in column A are numbers, that's why you don't need to put quotes around the comparison value (in this example 123).
    If the values in column A had been text values, which could include numbers stored as text, you'd have had to put quotes around the comparison value.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks, Hans. What do I need to do to make sure I am consistent? That is, how do you store numbers as text?
    Thanks again.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you set the number format of a cell to Text, then anything you enter into the cell, whether it's text, a date or a number, will be stored as text.
    Another way to force Excel to store a value is text is to prefix it with an apostrophe ', for example '123.
    You can usually spot the difference easily: unless you have explicitly specified a horizontal alignment other than General, text values will be left-aligned, number and date values right-aligned.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Hans. Need additional help. Why don't the two formulas on sheet2 return the same number?
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The OR function can't be used in array-type formulas - although your formulas are not explicitly array formulas, the use of SUMPRODUCT makes them so implicitly. OR returns only a single value instead of an array of values, which is what you need here. That's why the formula with OR doesn't return the correct value.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='799841' date='25-Oct-2009 15:01']The OR function can't be used in array-type formulas - although your formulas are not explicitly array formulas, the use of SUMPRODUCT makes them so implicitly. OR returns only a single value instead of an array of values, which is what you need here. That's why the formula with OR doesn't return the correct value.[/quote]

    Thanks.

Posting Permissions

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