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

    'SUMPRODUCT' with 'IF' (Excel 2000)

    I want to do a SUMPRODUCT of two arrays. However, some cells in the array contain strings rather than numbers. Is it possible to do SUMIF type of condition to SUMPRODUCT ? For example

    1 3 | 5 6
    2 8 | 7 9
    5 B | 1 4

    In the above case, it would leave B and 4 alone and just SUMPRODUCT the rest ?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: 'SUMPRODUCT' with 'IF' (Excel 2000)

    If I have this data:
    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center valign=bottom>1</td><td align=right valign=bottom>1</td><td align=right valign=bottom>3</td><td align=right valign=bottom>5</td><td align=right valign=bottom>6</td><td align=center valign=bottom>2</td><td align=right valign=bottom>2</td><td align=right valign=bottom>8</td><td align=right valign=bottom>7</td><td align=right valign=bottom>9</td><td align=center valign=bottom>3</td><td align=right valign=bottom>5</td><td valign=bottom>B</td><td align=right valign=bottom>1</td><td align=right valign=bottom>4</td></table>
    and use:
    =SUMPRODUCT(A1:B3,C13)
    it equals 114 which is what I would expect. It seems to ignore the text fine.

    114 = 1 * 5 + 2* 7 +5 * 1+ 3 * 6 + 8* 9 (ignoring the B and 4)

    Am I missing the problem statement?
    Steve

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

    Re: 'SUMPRODUCT' with 'IF' (Excel 2000)

    Steve,
    Many thanks. I used SUMPRODUCT(A1:B3 * C13) or used "*" instead of "," which is why it did not work.
    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
  •