23. SUMPRODUCT: Power and flexibility Startup and investor excel model training

Intro to Excel model training

This is part of a series on Excel model training focused on helping founders and investors to actually enjoy and get proficient in excel. It’s such an important skill to have, and there’s no excuse to not be able to do all the ‘advanced’ stuff, let alone the basics.

This is a pragmatic course where you will only learn the most useful things; those that I actually use regularly. We’ll go through the obvious applications, but also include some hacks I’ve developed over the years.

To get the excel example for this and all the other instalments, subscribe with the pretty box on the right and you’ll get the excel tip of the day sent directly to your inbox. Within a month and ten minutes practice, you’ll be a boss in no time.

You can join up to the training here.

What’s the point of the SUMPRODUCT?

SUMPRODUCT is pretty dope. There’s a load of things you can do with it.

Multiple stuff

In one case, you can use it to calculate the product of two arrays (two columns or rows of data). So let’s say you want to multiply two columns of data against eachother. You could say A*1, B*2, C*3 etc, but that’s a pain in the bum. So you select the two datasets and multiply them in one formula. Super simple.

You’re going to learn just this in the exercise. This is a lot easier to follow inpractice than the description above. You’re going to calculate the weighted average sale price for your ecommerce company.

Add up stuff selectively or find it

Now the second use is way cooler. SUMPRODUCT can do the same thing as a VLOOKUP, INDEX/MATCH and SUMIFS. In excel there are a tonne of ways to do the same thing, you just need to know what the best one if to do the job.

So you can use SUMPRODUCT to conditionally add up values, or find a value.

How does it work?

So how does SUMPRODUCT work. Well it uses arrays. An Array is basically a list of data points. say 1 to 10 and a to z.

The arrays need to be the same length to work. A rugby team has two teams of 15 to play a game, right, otherwise there is no game played? So ALWAYS make sure the arrays are the same dimension.

So if you want to multiply an ‘array’ of 15 prices and 15 sales numbers, then boom this is awesome.

But what if you want to multiply only some of those 15? Let’s say you just want the forwards, the scrum team. That’s 8 people, so we have to get rid of 7. When you add a condition in SUMPRODUCT, all the backs disapear and holes form in the array. Hole times a hole is a hole (ie zero)! So what is left is what you are searching for.

I know this is boring, but you need to understand what’s going on so you can learn to invent formulas yourself.

Yes invent. Modeling is about being creative with all the functions in excel and how you structure data to solve problems. So you need to understand how things work.

Whatever, let’s get into the details.

How do I do these things you speak of?

Let’s multiply your sales and prices to get the total revenue.

The manual version involves 4 formulas.

SUMPRODUCT is one. Whoop.

Look below:

I use this ALL THE TIME building models. You can use it to forecast the requirement of staff you need for example.

Now, let’s get conditional. Sales come from somewhere… clients, right?

So what if you want to know the sales you get from Google and not Facebook.

To do this manually is an ever bigger pain than before. We would have to filter out the revenue manually with an if statement… see below. WAY too much work!

Now to do this all fancy, let’s get down in funky town.

We use the same formula as before and then add in the conditional part which is:

*(G15:G17=H12))

This looks at the names of the clients in an array and then filters out the ones which are not = to H12, Google. So the 180 is the revenue just from Google and not Facebook. Following me?

Now I purposefully wrote the formula with both comma , and multipliation * to make a point.

You can multiply two arrays with either comma OR a multiplication.

BUT, you can’t filter with a comma , it has to be a *.

Try it. It won’t work. I don’t know why this is the case, it just is!

So if you have a crappy memory maybe just remember *.

SUMPRODUCT can do more, you can use a / divider instead of a * and you can divide the two arrays. I just never do that. Anyways.

You can also add in multiple conditions into the formula. I normally only really have two conditionals in a formula, at most 3. That’s just how my models seem to be structured.

One random thing I’ve done, but only once is to use TRANSPOSE() on a row when multiplying against a column. TRANSPOSE turns the row into a column ;). This is an example of thinking…. Thinking I can’t be bothered to add more data so how can just maniupulate with formulas.

Cool? If something isn’t clear, please let me know in the comments!