Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SumProduct Formula

    Hi,

    I am trying to multipy column "A" by column "B" and sum the results where the value for the corresponding cell in column "C" is 1. I have successfully used the SUMPRODUCT formula to multiply the array, however would anyone know how to incorporate an IF statement, or use the SUMIF formula to only add the product of the two cells when the value in column "C" is 1?

    Thanks,
    Mike

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: SumProduct Formula

    If your data is in the Ranges A1:A30, B1:B30, C1:C30, the following formula gives the results you want:
    {=SUM(IF(C1:C30=1,A1:A30*B1:B30))}. As it is an array formula, you enter the expresion between the{} and then press Ctrl-Shift Enter

    If all the cells in C = 1 it gives the same result as =SUMPRODUCT(A1:A30,B1:B30).

    Hope that is what you need

    Andrew C

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SumProduct Formula

    Thanks for your reply Andrew,

    I entered the formula and did the ctrl-shift-enter and nothing really happened other than it displayed the formula in the cell I typed. The formula as it stands reads #VALUE!. I think I must be doing something wrong with the key sequence or the {}.

    I made a test workbook using rows 1-9, and this is what I put into the cell:

    =SUM(IF(C1:C9=1,A1:A9*B1:B9))

    I tried putting the {} around it then ctrl-shift-enter, but it still says value. Any ideas what I am doing wrong?

    Thanks,
    Mike

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: SumProduct Formula

    I am sorry, I should have made it clearer that you do not need to type the {} brackets. Enter the expression without {} and then Press Ctrl+Shift+Enter (all together) Excel inserts those characters itself.

    I still don't understand why it gave the #VALUE error if the data referenced was numerical.

    However hope that works, and sorry once again

    Andrew C

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SumProduct Formula

    Thanks Andrew,

    I retyped the formula and followed your instructions and it worked perfectly. Thanks for your time.

    Mike

Posting Permissions

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