Instructor: All right, let's take a few minutes
to talk a little bit more
about this mysterious SUMPRODUCT function,
because I know that some of you guys watched that last video
and you're left still kinda scratching your head
because it's not quite clicking,
and that's totally okay, totally normal.
I felt the exact same way
when I first started learning this function
because it really isn't one of the more intuitive functions
that Excel has to offer.
So what I'd like to do here is actually practice an example
in the Excel environment so that you can see
exactly what's going on behind the scenes
and get a sense of how SUMPRODUCT can be used
in some real world contexts.
So if you open up your section four exercise file,
I've added two new tabs here.
One's called SUMPRODUCT, which we're gonna cover now,
and the other is called COUNTIF and SUMPRODUCT,
which we'll cover later in this section.
If you don't see those two tabs,
just means that you have an older version
of the exercise file.
Just head your course dashboard,
click into the course Content tab
and click All Resources
to download a fresh copy of this workbook
and then you should see all of these tabs shown here.
So what the heck are we looking at here?
Well, this is an example of something called
a transportation matrix.
And in this particular case,
we're looking at four different factories,
Boston, New York, Chicago, and Oakland,
and four distribution centers,
Miami, Dallas, Seattle, and Baltimore.
And the idea here is that we're shipping products
from our factories to our distribution centers
where they can then be mailed out to our consumers.
And this should look pretty familiar to those
who have taken my Pro Tip for Power Users course.
This is the same matrix that we used
for our optimization demo using Solver.
In this case, we're gonna keep things
a little bit more simple,
but the same concepts are gonna apply.
Basically we have two pieces of information here
in our transportation matrix.
In the top matrix, we've got shipping costs per unit.
So that tells us how much does it cost to ship a unit
from each factory to each distribution center.
So as you can see, it's very cheap to ship between cities
that are close in distance or proximity,
only about $4 from Boston to Baltimore,
under $2 from New York City to Baltimore.
And it's much more expensive when you're shipping
all the way across the country,
like from Boston to Seattle, for instance.
So that's the first piece of information that we have.
The second piece is the actual number of units
that we're shipping from place to place.
So in this case,
we're shipping 50 units from Boston to Miami,
150 from Chicago to Dallas, and so on and so forth.
Now, our bottom line goal or objective
is to calculate the total shipping cost here in cell E18
which is a function of both the units shipped
and the cost per unit.
So one approach that you might be tempted to take
is to kind of go cell by cell here and say,
okay, the 50 units that we're shipping
from Boston to Miami in cell D13,
they cost $15 each right here in cell D5.
That means that it's $750 to pay for those 50 units
to get from place to place.
And since we've left our references relative,
we can just drag that over and down
and then sum up that entire range to get our answer.
In this case, $12,158.30.
And that's totally fine, that's accurate,
but SUMPRODUCT is gonna allow us to calculate
that exact same result without this intermediate step.
So let's go ahead and delete that.
And now instead of 16 functions,
we're gonna write one single SUMPRODUCT function
here in cell E18.
And all we're gonna do is type SUMPRODUCT
and all it needs for arguments are the arrays
that we're going to multiply and then add together
and array one is D5 through G8.
This function's not gonna be moving
so I don't have to fix that reference,
can just leave it as is.
And the array or matrix number two is D13 through G16
and it's as simple as that.
Close it off, press Enter, boom,
get the exact same answer, $12,158.30.
So essentially the SUMPRODUCT function here
is running all of those same multiplication problems
that we did with those 16 individual functions
and it's embedded them into its own single formula.
And then once it calculated all of those products,
it sums them together just like the name suggests,
SUMPRODUCT, and it gives us our answer.
So hopefully that's helpful to kind of give you
a little bit more of a sense of how this function's working
and how you might be able to apply it
in a real world scenario.