Solved

# Sum the products of columns Excel

Posted on 2012-04-04
302 Views
Hello All,
I want to add products of three columns in a sumif expression type or similar way. Like as long as the event is the same, sum up the TOTAL (products of A*B*C)
Like….if you look at the worksheet here…this “sumif” or something similar will do like this below:
a(A*B*C)+ a(A*B*C) +a(A*B*C)

b(A*B*C)

c(A*B*C)+c(A*B*C)+c(A*B*C)

Thank you
R
sum-the-products-along-columns.xlsx
0
Question by:Rayne
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3

LVL 81

Expert Comment

ID: 37807350
Place in H5 and copy down:

=IF(C5<>C4,SUMPRODUCT((C\$5:C\$10=C5)*D\$5:D\$10*E\$5:E\$10*F\$5:F\$10),"")

Kevin
0

LVL 81

Expert Comment

ID: 37807358
0

Author Comment

ID: 37807449
Hello Kevin,

Thank you . Great.One Issue; since the rows will be increasing daily, is there a way to make the source column ranges dynamic: in that ranges will get bigger with more rows?

Thanks
R
0

LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 500 total points
ID: 37807617
Simplest solution is to set the number of rows to be more than you will ever need:

=IF(C5<>C6,SUMPRODUCT((C\$5:C\$10000=C5)*D\$5:D\$10000*E\$5:E\$10000*F\$5:F\$10000),"")

Kevin
0

LVL 81

Expert Comment

ID: 37807626
What you CAN'T do is pass the entire column in to SUMPRODUCT.

Kevin
0

Author Comment

ID: 37808148
Thanks Kevin for the help :)
0

LVL 50

Expert Comment

ID: 37808208
In fact it is possible to use the whole column in SUMPRODUCT in Excel 2007 or later versions, although probably not advisable due to increased calculation time. This version works, with syntax adjusted so that text in row 4 is ignored

=IF(C5<>C6,SUMPRODUCT((C:C=C5)+0,D:D,E:E,F:F),"")

regards, barry
0

Author Comment

ID: 37809399
Thank you Barry
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.