SUMPRODUCT Multiply and Divide

[ddd2009]

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.

[TABLE="width: 663"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]652.72[/TD]
[TD]0[/TD]
[TD]4.22[/TD]
[TD]0[/TD]
[TD]488.52[/TD]
[/TR]
[TR]
[TD]594.72[/TD]
[TD]0[/TD]
[TD]2.48[/TD]
[TD]0[/TD]
[TD]594.72[/TD]
[/TR]
[TR]
[TD]556.36[/TD]
[TD]0[/TD]
[TD]1.33[/TD]
[TD]0[/TD]
[TD]556.36
[/TD]
[/TR]
</tbody>[/TABLE]

=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.

 

[Marcelo Branco]

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.

 

[ddd2009]

Thank you M yes that worked, much appreciated.

 

[Marcelo Branco]

You are welcome. Glad to help.

M.