Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Sum the products of columns Excel

Posted on 2012-04-04
8
Medium Priority
?
312 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
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

783 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