SUMPRODUCT

Say you're going to a concert with your 3 best friends: Drake, Josh, and Megan. Since your hefty paycheck just came in, you decide to treat your friends and pay for everything. This includes tickets and different kinds of merch.

You decide you should probably prep your wallet and attempt to predict the prices of everything for the night.

Ticket: $125
Shirt: $35
Hat: $15

However, you know that Josh won't want a shirt, and that everyone but Megan will want a hat, Drake will want 2 shirts, and you're going to want 3 shirts for your siblings... and so on. It quickly becomes complicated, and you realize that you should probably just create a rundown of how much of each you'll be purchasing.

Using the above table and a SUMPRODUCT function, we can easily determine how much this entire night out is going to cost you! That's because...

SUMPRODUCT multiplies the first, second, third, etc. values of each array and then sums the products together.

Coding a SUMPRODUCT

Understanding a SUMPRODUCT is the tough party. Luckily, the easy part is coding it!

Here is the template for the SUMPRODUCT function:

=SUMPRODUCT(array1, [array2], [array3], ...)

So, what are the arrays? In this case, they're just the column of values in our table.

=SUMPRODUCT(B2:B4, C2:C4)

For context, the formula that will be going on behind the scenes is...

($125 x 4) + ($35 x 7) + ($15 x 3) = $790

First, SUMPRODUCT takes the first values of each array and multiplies them together...

Then, it moves to the second value of each array and does the same...

Lastly, it multiplies together the third values of each array.

After doing this, it sums all of them together to get $790.00!

In other words...

SUMPRODUCT multiplies the first, second, third, etc. values of each array and then sums the products together.

One crucial note:

With SUMPRODUCT, your arrays must be the same size.

If they're not the same size, you'll get an error!