SUMPRODUCT Multiply and Divide

ddd2009

New Member
Joined
Oct 3, 2014
Messages
10
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.

ABCDE
652.7204.220488.52
594.7202.480594.72
556.3601.330556.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.
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,257
Messages
5,467,614
Members
406,544
Latest member
Aditya_Shanmugham

This Week's Hot Topics

Top