1. ## 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. ## 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. ## Re: SumProduct Formula

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. ## 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. ## 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
•