SumProduct Formula

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Thanks Andrew,

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

Mike

Reply | Quote