SUMPRODUCT | CSE 148 – Excel Exam 2

Still feeling nervous for your exam? Check out the Excel Cram Kit (Exam 2) for practice modules that emulate the difficulty that you'll face on your exam. Each module includes a thorough step-by-step explanation where I walk you through the entire problem solving process!

SUMPRODUCT explained

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!

Practice problem

Let's say you have the following list of groceries, their cost, and the quantity you bought of them.

Using a SUMPRODUCT function, determine how much you spent in total.

=SUMPRODUCT(B2:B6, C2:C6)

Excel (Exam 2) Follow-along Guide

It's no secret you retain info better when you write it down. That's why I created the Excel (Exam 2) Follow-along Guide for you!

As you come upon key concepts highlighted in yellow, like this sentence here, you can fill-in-the-blanks on your Follow-along Guide so that you remember all the important stuff for later!

You can obtain the Excel (Exam 2) Follow-along Guide for FREE by entering your email below!

Lastly, let's learn how to use 3D cell references!

Follow-along Guide

Fill-in-the-blanks for highlighted, key points in the concepts below!

Free Resources

Each exam concept broken down in simple, real-world examples!

Practice Problems

Step-by-step walkthrough for each of the questions you need to be ready for!

"I got this review for my 2nd CSE exam after not doing so well on the first one, and it was so helpful! Will definitely be using again in the future."

Erin Graham
Oct. 26, 2021

Rating: 5 out of 5.