SUMPRODUCT to Calculate and Summarise Data

Demo of Excel's powerful SUMPRODUCT function calculating commissions, weighted averages and summaries!

SUMPRODUCT is a great function to have in your back pocket. Although you might not need to pull it out loads, when you do it really works wonders! It's extremely powerful and versatile, and has many applications. However, it comes with a health warning as it can be conceptually difficult to use, understand and code, and is quite memory intensive.

This video is Part 1/2 in a powerful series. In it, we look at how we can quickly calculate commissions and weighted averages - particularly useful for pricing analysts. We also take a step into creating summaries in a similar way to SUMIFS, but performing additional calculations in the process. We even combine it with SUMIFS to create some more complex average price summaries.

Head to my YouTube for Part 2 where we take things a step further by creating more advanced summaries with multiple criteria and look at achieving something that SUMIFS can't - summarising data where criteria is in 2-dimensions...you may need to take a look for that to make sense. This is a series not to be missed!

Transcript

Hi Guys, I’m Dan Stockdale, and welcome to Part 1 of my 2 Part series on SUMPRODUCT. Now, SUMPRODUCT, this isn’t a function I use loads but when I do it’s super useful. But it does come with a few warnings. Firstly, if you make a big complicated SUMPRODUCT that references large ranges, does lots of things and you copy it many times into lots of different cells then you do risk slowing down your spreadsheet or model as it’s a memory intensive function. Not dissimilar to array formula…which are to be avoided! Secondly, it can be conceptually difficult to use, understand and code.

So we’ll start with some basic uses of SUMPRODUCT, move onto Weighted Averages, and then in Part 2 we’ll build up to some more complex applications calculating and summarising data. With a lot of my videos I try to put these functions into a range of worked examples to demo their application rather than just show a really basic example and then kind of leave the rest down to you.

So some of these might seem a bit complicated but they’re almost actual, real life applications of the function that you’d actually have to do. And if you like what you see please Like and Subscribe and I’ll keep producing more!

The first question is asking us "Use SUMPRODUCT to calculate Total Commission payable to Sales Reps". Here we've got some information on sales reps. We've got the Sales Reps here. We've got their Grade. How much they've sold in the year. Their Commission % and therefore their Commission $. So the Commission being just the Sales multiplied by the Commission %.

So here we want to calculate just in one cell what this essentially is coming to which is 284,713. We're going to use SUMPRODUCT for that and it's actually really basic when we do the first example.

So we'll code SUMPRODUCT. I can just press Tab here and it opens the bracket. And all I'll do is select Array 1 which will be Sales, comma, and then select the 2nd array. I'll close the brackets, press ENTER, and then explain what's going on.

We can see we got that 284,713 which we just saw down here which is the total of this Commission. Basically we've got here 2 arrays. What SUMPRODUCT does is it takes the 1st cell in the 1st array and multiplies it by the 1st cell in the 2nd array and then the 2nd cell in the 1st array, multiplied by the 2nd cell in the 2nd array, adds it all together and it keeps going down until it gets the total.

The arrays you choose need to be of the same or similar dimensions and so by that I mean with these 2 arrays they're both 1 column wide and 9 rows deep. Now, if I was to extend this to 2 columns wide and 9 rows deep they'd be similar in that the rows are the same dimensions but now the columns are different. I could press ENTER - I'm actually going to get a #VALUE! result here, so I need to change - when I've extended this to 2 columns wide I actually need to change the format of my SUMPRODUCT slightly.

So I'll add brackets into this 1st array and then add a multiplication sign, remove the comma, then add brackets around this 2nd array as well, and press ENTER. We get a result which is ridiculous because we don't really want to be multiplying these cells by this commission, by this commission etc.

But it's to demonstrate we can write this in a different way and pick a different dimension of array as well. So if I actually change this back to what we had before, we've still got the same answer, so this kind of shows there's two ways that you could write a SUMPRODUCT. We could do the first way we originally showed with just a comma between, close the brackets, and in both of them we basically get the same answer. Now, when you use SUMPRODUCT for further purposes, in the examples we'll be showing, often we'll need these brackets. My advice is write your formula in a consistent manner. Personally I always go for brackets.

We'll move on to Example 2 "Use SUMPRODUCT to calculate Total Commission payable to Sales Reps whose cells are at least $550k." So we're saying if these cells are $550k or more we'll multiply the Sales by the Commission % etc. So we're adding in a condition/criteria here. To write this (and then I'll explain) we type =SUMPRODUCT(( opening brackets for the 1st array. We'll select in the same way these Sales and then the multiplication sign select the 2nd array, the Commission. Now we'll add in a condition - we go back, we select the cells again and then we want to put a '>=' the criteria, close the brackets, then close the brackets of the wider function, press ENTER, and it's now going through and it's essentially looking at these and if it's $550k or more then it'll do the multiplication. If it's not, it almost cancels it out, forgets it and doesn't sum it up at the end.

To explain a little further, the last part of that SUMPRODUCT creates a third array in memory that we can’t see. And it comprises of 1s and 0s. There’ll be a 1 if the condition is TRUE i.e. the Sales are $550k or greater, and a 0 if they’re not i.e. the condition is FALSE. So there’ll be a 1 for Matt Saunders, a 0 for Tara Heal, 1 for Frankie Markot etc. It’s doing something similar to what the Flag column does in the next question. The three arrays are multiplied together and then summed. And of course if you multiply something by 1 the result is retained. If multiplied by 0, then the result is 0.

So when I say if the condition isn’t met then the calculation is kind of cancelled out, I just mean it’s multiplied by 0. We actually kind of see this in the flag column in the next example. And it’s important to remember all of this when thinking about how SUMPRODUCT works, particularly in the examples in Part 2 of this series – the 2nd video.

We can actually prove that this is working by looking through these and only adding the Commission where the Sales is $550k or more. So I'll do that manually. If I select all these cells and miss out these two then we can see our Commission is $237,495 which is the answer that we get, so we can see it's working. We can now play around with this and it will change the result. We could put it up to $600k and it's just cancelling out certain rows, not adding them to the result.

Finally, moving on to Example 3, it says "Use SUMPRODUCT to calculate Total Commission payable to Sales Reps whose Sales were at least the Sales Floor for their Grade." So where this was the condition in Example 2, we're now saying actually it's dependent on their Grade - if they're a Manager then their Sales need to be $700k or more, if they're a Senior Associate $600k or more etc.

Now, I kind of thought with this example there probably is a way to do this - to write it out in a SUMPRODUCT but what you get is a very complicated formula which is really untransparent and so I just want to show you how you could use for instance a Helper Cell or a flag to break down your formula, and that's much better practice when you're modelling or producing analysis because it helps other people understand what's going on and makes the formula that you're entering shorter.

So I'll use this column which I've titled 'Flag' and I'll put in there an INDEX & MATCH function. If you're not familiar with INDEX & MATCH, it's a great way to look up information and absolutely vital for advanced modelling, so check out my video on that. It's really useful. It can transform some of your work. So for the MATCH I'll be looking up 'Manager' within this Lookup Array and then I'll put a 0. Now, I need to anchor these - I need to put a $-sign in front of the rows and then I'll copy the formula down.

So I've created a column where for each of the Grades it's just looking up the Sales Floor for the Grade. I'll add an IF function around this. This is an example of formula construction - using different functions together. I'll say "If Sales is greater than or equal to what I've found in that INDEX put 1, otherwise put 0." I'll copy that down. OK so now, where the Sales is greater than the relevant Sales Floor for the Grade we'll get a 1 if it's not we'll get a 0. So for instance here they're a Manager - 669k is less than 700k. Now, I'm going to use this in the SUMPRODUCT. All I'll do is enter the SUMPRODUCT and then Array 1 will be the Sales Array 2 will be the Commission %, and then Array 3 will just be this.

So we can see there we've got an answer. It's essentially going through each of these and it's multiplying the Sales by the Commission %, then also by the Flag. If it's multiplying by 0 it cancels it out. So that's quite a handy way of using Helper Cells to simplify our formula. That's a pretty simple formula. That's the end of our first examples.

In this second set of examples we'll look at how we could use SUMPRODUCT to create Weighted Averages. We're going to be building up to that, particularly in Question 3. But first we'll introduce this data.

We've got 6 gyms in New York - Chelsea, Soho, Tribeca etc. They've got different Membership Types - No Frills being the cheapest, Premium, and then Deluxe being the most expensive. We've got the Members # on each Membership Type of the gym, and then the Monthly Price they pay which reflects the Membership Type.

For this first example we'll look at calculating total Monthly Revenue by Gym and by the Membership Type. This will be similar to the previous example where we looked at multiplying Sales by Commission % to get Commission $. This time we'll be multiplying Members by Monthly Price to get Monthly Revenue. We'll be adding in a condition though - the Gym. So we'll work out the Monthly Revenue for each of the Gyms.

I'll type in the SUMPRODUCT and then same way as before I'll select this array and anchor that. Then select the Monthly Price and anchor that as well.

The term ‘anchoring’ refers to when we place a $ sign in front of the column letter and/or row number. If a column or row has been anchored then when we copy the formula across to adjacent cells, the column or row will stay frozen and not shift about.We can either manually place $ signs within the formula, or we can press F4 once to anchor columns and rows, twice to anchor just rows, or three times to anchor just columns. Give it a go!

If I was to finish the SUMPRODUCT there, that would calculate revenue for all of the gyms together. But we need to add in a criteria. We'll select the gyms there, anchor the rows again, and it's got to equal the Gym here. If I copy that down we can see the total is 2,628,722. We can check this. It's always good to check the calculations work correctly. If I was to do it quite manually then my total here is 2,628,722 which is correct.

So I'll now copy this formula down and then I'll change it slightly. The 'criteria range' or 'condition range' will be Membership Type, and the 'condition' or 'criteria' will be the Membership Type here. If I now copy that we get the same revenue. So we've essentially gone through, we've calculated the Monthly Revenue for each of these Gyms, then for each of the Membership Types, and just summarised it here.

Moving on to Question 2... I'll group these rows and hide them away. I'll press Shift + Space, then Shift + Alt + Right arrow, and this groups them. I just need to click this and you can see they're hidden away so we can still see this data for Question 2.

Question 2 asks "Use SUMPRODUCT and SUM to calculate the overall Weighted Average Price." Essentially to do that we need to divide Total Revenue by the Members. So I'll use SUMPRODUCT again I'll select Members multiply it by the Monthly Price this would give us Total Revenue. Then I'll put in a SUM function at the end which sums the Total Members. And that is the Weighted Average Price of $144.10. So that's pretty simple. Now we'll extend that slightly for Question 3. I'll group this again and hide it away.

For Question 3 we'll do a similar calculation but do it for each Gym. So instead of SUM we'll need to use SUMIFS. If you haven't used SUMIFS before then it's a super useful function. I use it a lot in modelling. And I've got video on that, so check that out. So with the SUMPRODUCT, I'll put in SUMPRODUCT and then we'll be selecting the Members and actually I need to anchor these rows. (It's always important to remember to anchor rows otherwise you can get the wrong result.) I'll multiply this by the Monthly Price, anchor that, and then we need to have a condition of the Gym so I'll select this as the 'criteria range' and it's got to equal the Gym here close the brackets of the SUMPRODUCT. And again here we'll essentially have Total Revenue by Gym.

Now, if I put in a SUMIFS. With the SUMIFS we'll be summing the Members at each of these gyms. I'll put SUMIFS here. The Sum Range will be Members. I'll anchor this and then the Criteria Range will be the Gym. Anchor that. And then the Criteria will be the Gym here. Note, when we do a kind of criteria or condition in the SUMPRODUCT, we have it like this - we just have the range equals the criteria. But in a SUMIFS it's a slightly different format. You've actually got to put that as a range and then a comma and then select the criteria.

So if I press ENTER now and copy down the formulas, then now we have a Weighted Average Price by Gym. If I take this, I'm going to copy this down here - it's almost recycling the formula just to save time. I'll paste the formula and I just need to adjust my Criteria Ranges. So we're no longer interested in Gym, just Membership Type. I'll shift this over to the Membership Type here. You can see this kind of makes sense - No Frills is about $89 and you can see it's $89 here, $100 here, $79 here. Deluxe is $238 - it's $213 here, $250 here, $243 here. So these figures sound about right.

So that’s how we can use SUMPRODUCT, helped in this instance by SUMIFS, to create a Weighted Average Price. I hope you’ve enjoyed video. You can probably see SUMPRODUCT is actually quite powerful but not necessarily straightforward! Definitely check out Part 2 of this series as it looks at a different application of SUMPRODUCT that can be great for summarising data in cases where SUMIFS can’t.