# Thread: SUMPRODUCT Multiply and Divide Thanks: 0 Likes: 0

1. ## SUMPRODUCT Multiply and Divide

Hi all

Help! I need to proportion how much in Column C relates to amount column E based on the gross amount in column A (C/E*/A) or (E/A*C) etc. If I manually calculate and add each row individually the total is 6.97 but my formula for all returns 7.30. My example below is a condensed version, I have many rows with 0 value in all columns.

 A B C D E 652.72 0 4.22 0 488.52 594.72 0 2.48 0 594.72 556.36 0 1.33 0 556.36

=SUMPRODUCT(--(Data!\$E\$2:\$E\$108<>0),Data!\$C\$2:\$C\$108)*SUMPRODUCT(Data!\$E\$2:\$E\$108)/SUMPRODUCT(--(Data!\$E\$2:\$E\$108<>0),Data!\$A\$2:\$A\$108)

Thank you in advance and any help on this will be greatly appreciated.

2. ## Re: SUMPRODUCT Multiply and Divide

Maybe...

Array formula
=SUM(IF(Data!\$A\$2:\$A\$108<>0,Data!\$E\$2:\$E\$108/Data!\$A\$2:\$A\$108*Data!\$C\$2:\$C\$108))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

3. ## Re: SUMPRODUCT Multiply and Divide

Thank you M yes that worked, much appreciated.

4. ## Re: SUMPRODUCT Multiply and Divide

You are welcome. Glad to help.

M.