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.

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

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

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