Solved

Sum the products of columns Excel

Posted on 2012-04-04
8
293 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 81

Accepted Solution

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

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Outlook Free & Paid Tools
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

777 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