I have two columns of numbers.
I need to write a formula to multiply the two columns together, and then add all of the products together.
In this case: (16*1) + (6*0) + (4*0) + (4*0) + (4*0) + (2*0) = 16
Normally I would just make a third column to hold the products, and then sum those.
But this table is huge, and I would have to add way too many columns for that approach to be practical.
2 Answers
You can do that directly with SUMPRODUCT, which does exactly the calculation in the question:
=SUMPRODUCT(A1:A6,B1:B6)SUMPRODUCT can also be used with two-dimensional arrays, and more than two columns to be multiplied and then added. See Microsoft Office Support
use an array function.
=SUM(A:A*B:B)and press Ctrl+Shift+Enter to finish your formula. It will end up looking like this:
={SUM(A:A*B:B)}from:
