?
Solved

Sum the products of columns Excel

Posted on 2012-04-04
8
Medium Priority
?
308 Views
Last Modified: 2012-04-04
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
Comment
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
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
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

by:zorvek (Kevin Jones)
ID: 37807358
0
 

Author Comment

by:Rayne
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 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

by:zorvek (Kevin Jones)
ID: 37807626
What you CAN'T do is pass the entire column in to SUMPRODUCT.

Kevin
0
 

Author Comment

by:Rayne
ID: 37808148
Thanks Kevin for the help :)
0
 
LVL 50

Expert Comment

by:barry houdini
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

by:Rayne
ID: 37809399
Thank you Barry
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question