Sum the products of columns Excel

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
RayneAsked:
Who is Participating?
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
RayneAuthor Commented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
What you CAN'T do is pass the entire column in to SUMPRODUCT.

Kevin
0
 
RayneAuthor Commented:
Thanks Kevin for the help :)
0
 
barry houdiniCommented:
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
 
RayneAuthor Commented:
Thank you Barry
0
All Courses

From novice to tech pro — start learning today.