The Awesome SUMPRODUCT Function - Guide and 5 Great Examples | Alan Murray | Skillshare

The Awesome SUMPRODUCT Function - Guide and 5 Great Examples

Alan Murray, IT Trainer, Coffee Lover and Lifelong learner

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
7 Lessons (31m)
    • 1. Introduction

    • 2. The Definitive Guide to the SUMPRODUCT Function

    • 3. Example 1 - Count and Sum the Sales from a Specific Month

    • 4. Example 2 - Count the Occurrences of a Specific Word in a Range

    • 5. Example 3 - Count the Unique Values ONLY in a Range

    • 6. Example 4 - Sum the Top 3 Values

    • 7. Example 5 - Two Way Lookup Formula with SUMPRODUCT


About This Class


In this class we explain the amazing SUMPRODUCT function of Excel inside-out. And then demonstrate 5 'real world' practical examples of its use.

This function is incredibly useful and is a kind of formula utility knife. It can help us tremendously when analysing data.

The 5 examples in the class will nicely showcase the benefits of learning SUMPRODUCT.

We will see SUMPRODUCT sum and count values based on multiple conditions, use it for a two way lookup and much more.

As always the exact files that I use in the lessons are available to download from the project section to follow along and practise. There is also a project to complete to test what you have learnt.

Ok, lets get going. Enrol and I'll see you inside.


1. Introduction: Hello and welcome to this class on the awesome Some product function. Off Excel. This is one off. My personal favorite functions is so useful, and we will be explaining it inside out and then looking at a few examples off why dysfunction is so beneficial to us now we're going to start with the definitive guide to it . So don't worry if you have not heard of it before or if you're new to it and you think it might be a little bit beyond you, we will be explaining it in depth, and then we'll be going on to see various examples of its use. So we're going to look at how it can someone count based on multiple conditions. But in start being more specific with them like someone and counting for a specific month by incorporating other functions into some product loss to see how we can use it to count all the occurrences of a word in an entire range off sales. That's something it is not easy to do without. Some product lost look, accounting, unique values. Excel doesn't have a function for that. It has account function. It has one to count blanks that's can't anything that's not blank, but it doesn't have one account the unique, so we'll see some products during that. We're also going to use it to some the top free values only on and finish off with. I look up so normally when we think of lookups, we think of a look out. We think off index and match functions. Some products can also be used to look up values and will be seen an example of that in this course. So without further ado, let's get into it in role now on, I'll see you inside. 2. The Definitive Guide to the SUMPRODUCT Function: Hello and welcome to this video tutorial from computer garver dot com. In this video, we're going to look at the some product function off Excel. Now this function is a hidden gem. It's a really powerful analytical function where a lot of people just not aware of what this functions capable, I'm going to run through some examples off this function. What now? Beginning with its classic form Now with some product. The function, as its name suggests, is meant to some the product of multiple arise. So in his first example, I had the price off products and a quantity of products on I want the overall total. And yes, you know, I could write formulas somewhere to multiply the price by quantity to find out the individual prices off these, And you might want that now confronted. What will they are? I can see in the bottom right hand corner. The overall total is 24. 25 is actually the bottom right hand corner there. If you add up the four values after, and that's what some products going to do all within one formula, find the products multiply price by constant for each one, then some them. Do not delete those for a moment and start up the some product function he called some product. It would start asking me for different arrays one or more raise. I'm not depart first awry as the range of cells with prices in, put in a coma and then select the range of cells with quantities in close bracket. Enter and I have that 24 25 all within one formula, and that is the classic use of some product. It's what has its name and what its initials intentions work, and that's quite. But Excel users have taken it far beyond its initial design, so over popped to the next sheet on my complex formula sheet over to try and show off a couple of examples of here or in here. Now I use some products quite heavily when I have complex work to do. And if anyone watching this video has has taken or at my sports league tables course we've seen any of the stuff I do around there, you'll know that I use um, product a lot. There is a lot of statistic calculations, quite intense quality logic and find some product somebody gin and other approaches. So using this example, we'll have here. I want to know how many orders in South America taken by this South person, uh, known Charlotte Harris So I can see that there's one there. Brazil and Charlotte. There's one here, Venezuela and Charlotte on Ah and have one somewhere which I I'm struggling to find. There it is Venezuela and Charlotte again. That was difficult. I knew there was free. Let's get her some product to do that. So we have free conditions. It must be Brazil. ALS, Venezuela and Charlotte Harris. Those three conditions are mixed off or and and logic within the formula. See recon do. It s me. Zoom in. It's easier to see on the video he call some product now for this first array. I'm going to open up. I prince sees a bracket and I'm going to select the range off countries a B two down to be 16 whatever that may be. And like make absolute and I'm going to test you that is equal to Brazil. All right, Brazil, when the inverted commas there because it's a piece of text and in a closed bucket against introduced a plus sign between kind off, think of as the or logic. When using some product in the way it's going to be used here, we'll open up another set of brackets on. I'll perform a similar condition to see if it's Venezuela, so select the country's make you absolute on asking was equal to Venezuela. Run out of room here when I her closing bracket. So that's the or logic done. Is it Brazil? Or is it Venezuela using a raise kind of stuff you can't really get off. It functions because he's a raise like that in its classic form. Now I'm going to sit another set of brackets around what I've just done because I wanted to do that conditional element before the next bit. Some were used brackets to stick plate, the order of calculation. Oh, then put in a multiply. So I'm my asterisk on an open set of brackets again for the next criteria. I want to know if the list of cells people Ah, a couple short there c two to C 16 is equal to the contents of every and where, in this scenario, I'm reference in the sale that currently has Charlotte Harris written inside it Close Bucket, and that should be job done. I'll put another close bracket for some product. See appear in black. So color an Easter try and help me at trace myself. Food formula. A little presente I have an answer free, which we know to be correct, cause in these 15 on bike sales, we looked at that to before we got going to some product less kind, offer an alternative to things like counties and let d count and agriculture and some of the cell functions that can perform this kind of work is an alternative and in some ways probably a preference well mixed up the or, and the and logic there was quite clever. It's the kind of thing that some of the functions is just not capable of on their own by design like Countess. But it's also easily tweaked for other types. Off aggregate, for example, I could select that sell one copy over, which is why I was making the references absolute. I knew I was going to do that double clicking G free to have a look at the formula I've got in there on what I'm going to do in the end here, he's at another multiply, and I'm going to multiply it by the South totals on. The reason doing this is to convert that into a some so a multiplying it by those figures, those totals. I'm now going to get a some off the back of it, I hope or should make absolutely guess, But I'll leave it as it is. Press enter, maybe quickly. Apply some for matins. It looks better. And now I have a total cells just shy off £2000 so I can easily switch it from account to a some some sort of brilliant for the summoning. Counting variations. Not so good. Others. If you want your media and your you mean average is your standard deviations there, Maybe you want pivot tables or or another technique. Some product is a really, really powerful formula. Great for these kind of intense calculations. That site by person have used them a lot in my sports league tables work which haven't course on, and you may find your benefits tooting too 3. Example 1 - Count and Sum the Sales from a Specific Month: now, in this first example, I'm going to use to some product function to count the number off orders for my specific month in column A and that month has been specified in Cell D free so company I'm interesting month to a K February, so I'm going to use some product function. But I'm also going to use the month function so that the month function can extract the month from each those dates and then some products will count the necessary once. So some product is an alternative array formula, which is what makes it so versatile when so special to excel users. If any of you watching this are completely no to some product, you may benefit from this video, but it might also be worth checking out a link. I having a description of this video to a general guide on some product. If you're new to this, this may seem a little bit crazy part. Let's it in. So e free begin to type my some product function, and it would prompt me for my arrays. So I'm going to put open bracket for my array, and I'm going to enter the month function and provide the month function with that array off dates. So normally, if you've used the month function, you normally to play a single cell to it. Buying, providing an array of cells, arrange some product can handle this. Alternatively, you are writing a ray formulas for this kind of stuff. I'm gonna put a close bracket for the month function and ask you is equal to the value in D free. That's where I have that number two written. My formula, unfortunately, could not going over it right now. She can't really say acceptable red outline off the cell of Selected. But that is all a close bracket for that array, and I could provide Maura raise if needed. And some of the other examples in this video will do that. For now, though, is another close bracket for May, and I'm going to press enter now that returns a zero no warders, apparently, but I can see there's a February there in Row five, a February in roast seven on a February in row 11 so we also should be free, not zero. Now, let's have a look at why that's doing that and correct it. I'm going to double click on my formula and a free I'm going to highlight the array part of that some product. So before it actually gets to the some product bit, what's that doing? I'm not going to press my F nine Key F nine, so it manually calculates that section, and now we can see what that's doing. And I'm seeing I'm getting out. I kind of folks, folks, folks, True, that's the 1st February that and then false. True. So marches not Row seven is next February to see. It recognizes the February the month but is perfectly. The logic is perfect. That's a working. It knows that February's The problem is when it gets to the summing part off some product because the some function does not like these truths, and fault is how can I add up? A. True that doesn't make sense. So I'm going to press escape right now, go back into my formula. And just before the array put two hyphens. Now, for those of you have done a little bit of some product before some quite bots formulas, you will probably recognize that it's a very common question. People come across this and what the heck did a hyphens dough? Or they are going to convert those truths and folks is toe ones and zeros, and then some product can react to those ones that's going to really like that. Save a press Enter. Now I get three. There are three orders in February, and if I do go into that formula and run just that section of code, you will now see that those two hyphens, that double hyphen as converted to truth toe ones and the false two zeros ensuring that might my summon off numeric values will now work. Now, I've just added another cell in here for a total. What I'm going to do is just take her. Just make those cells absolute to safe any problems here on. I will copy that some product formula, and I'll stick in this cell as well. And instead of account off how many orders or you know how much money as well. And to do that, all I need to do is at the end of some poor up to the end of that array. So there's a ray one, then the that poor comma someone array to, and I'll highlight the South figures. So this is making the most of the products be of some product. It will multiply the values in month, but a that's a by a be sort of ones that true in a by B and then summit so but doing it multiplication, we think to be some in only the February ones. What persons are there? We go for free five. Let me set a February ones in this rather smallest right now and at the bottom of the screen. You may be able to see that it says for free five that we may have to quickly do it yourself. 1 to 81891 18 is for free. Five. So I managed to find out how many and how much for a specific month which somebody could in cell d free just change in a month of interest and that will react to that data being specified. So is example number one off this incredible some product function 4. Example 2 - Count the Occurrences of a Specific Word in a Range: Okay, Some product function. Example. Number two on my wish to be able to count the occurrences off a specific word or words within this range of sales. In column, I said the word or words that I'm looking far has been entered into cell C two and I want to return the number of occurrences in day two. So I'm obviously you tin some product, But what else do we need? What? I'm going to use a function called find now very briefly. For those of you who may not have done this before, the find function can be used to find a piece of text. And what it would do is return the starting position off that piece of text if it finds it so I'm typing the find function right now. Let's imagine I want to find the word ginger and mistakes right away. I need that in these double inverted commas within the cell next to me. There a seven. Just a zit demonstration and I won't worry about a starting position for that. Keeping his brief, you can check out information about the find function elsewhere. This is all about some products in their examples But if I run that on that cell that tells me that is, they're basically it begins from Position eight s. So we have 123456 spaces. Seven. Number eight is the first character for the G for Ginger. But if a search for something nets that that that's not there like Ranger, they're not getting air a message. So if the find function brings back a number, I know it's present, and I want to know how many times has something worth is present. Suck in Cell D two. Here we go, with the some product function and full the awry, I'm going to provide it with a couple of hyphens. Good morning. In a second. Obviously we used. In a previous example, we'll see one a second opening bracket, and I'm going to use the ease number function before then, start typing the find function. I'm going to get the find function to search for the word or words and self C two. Come on within that range off cells are you to tau nine. In my example closed bracket. As I mentioned, we're going to ignore the final question there. Another close bracket for is number and a another close bracket for a but the array and for some product lower brackets going on there. So the find function will look for the occur. The value in cell C two in that range of cells, each cell at a time that will return a number or an error message is number. Is there waiting? If there's a number that would spate, true or false if there's not. And unlike in the previous example, there two hyphens there to comfort the true or false values toe one and zero. So then some products can. Some them. So our presente, apparently Oreos, is in there twice. Eso it's once in self a seven on once in a five. But if I was to type in, for example, custard creams and press enter at that, it's in their four times. It's in a two, a three baba i six and I ate. So this is some product again, now being able to count the number of occurrences a word or words within a range of cells, very useful. If you're kind of analyzing feedback and comments and the the kind of stuff that you're going to get in large volumes of text in the column. Okay, let's go on to see what else some products conduce. 5. Example 3 - Count the Unique Values ONLY in a Range: now, in this example, we're going to look at use in some product to count only the unique values in a range. Now, this is a very useful skill that I'm asked for quite a lot. You know, we can use the Count functions account. You know how many orders, how many training sessions. But people often want to know the unique values. How many different products to be? So how many different training sessions? Not just how many in total. So for this example on counting these names. So some off the Madu placated like like Sally their free times and Robert a couple of times . So I don't know. Hamli names honor how many different names have any unique names. So some products going in Cell C to hit and what will wrong on function? Some product and what I want to do here is I'm going to divide number one boy a count if function, and I'll try and explain how this is working as I go. So I put in this county function that problems before range of cells are provide a 2 to 8 10 comma was the criteria to look for in that range I'm going to provide the exact same right. So I'm going to count, for example, how many times Robert occurs in that range. Times James occurs in that range have times Sally ACOs in that range that's going to return how many times that they're found that is there going to be divided by one. So let me see this in action is closed bracket for count. If closed bracket for some product and press enter, the answer is six. So you got Robert James Sally as three. Gemma, Andrew and call. The answer is six. Now the way that works let me go into the formula is count. If we'll go and do this, that you will say how many times each name occurs that is me using the F nine key on my keyboard again there to manually calculate. So he told me that Roberts there twice. James's there once Sally is there three times, Roberts there twice and so on for each name in that range of Got that. These values are not divided by one so far wrong. That's bit of the code. You get this. So is dividing thes by once, and I'm getting your 0.5 on getting one extreme suddenly there once sat is there three times. So I've actually got 1/3. They're not went free free and so one. And then these value to some because of some product. And that overall brings me the six. And that's how this clever little function works but can having times each one, then divine it by one, gets decimal points if they occur multiple times and any over some provides a very clever way off, counting only the unique values in a range. OK, that is some product function example. Number three. 6. Example 4 - Sum the Top 3 Values: Okay, Some product function. Example. Number four. Some. The top three values on this tutorial could easily be adapted to find the top five values on top 10. Or even, indeed, the the bottom five on the bottom two on the bottom 10. This is quite versatile on the way I'm going to do. This is some product with the large function. It's very quickly. For those of you who may not know, this is the large function. So you may be aware of the max function returning the biggest value in a range large can return that the one that you specify, such as the second largest, third largest fifth largest. There is also a function called Small, which is what you could use for the bottom five. Bottom to that kind of scenario. Stick with large at the moment. That is going to probably for the array, which is going to be the bunch of South figures and in the Cape value. Like what? What are you interested in the second largest third largest? That kind of stuff, now one of the top three, not just what's the third biggest? I want to order 12 and free and this is what is some good about some product, this array function. It can handle free at the same time. And some of them he's not just a standard large function here were putting in some product where extending what it's capable off. So I start off with my son product function here, and then I just start typing large. Lastly, Array. I provide the bunch off South figures the values are looking in. Comma goes to Cape Value. Now, in some curly braces, I'm going to put one comma, two commas, three now, obviously Kentucky, Whatever numbers you want there, as many as you want. Whatever you want. You know I'm gonna have 12 had free the top free closing curly brace. So provide in the Cape values an array of giving it free values 12 and free in the range. Beat up to S. O. B. Two to B 12. I'm going to close my large function and enclosed some product. There's some product. Will some those top free That large returns answer is 562 The top three values are 188185189 That is the free together 562 That is the some part function, someone only the top free values and as a reminder, this can easily be adapted to using the small function to turn, You know, some of the minimum values from a range, if that's what you need it. 7. Example 5 - Two Way Lookup Formula with SUMPRODUCT: Okay. The fifth and final example is seeing some product perform a two way Look up. Yes, that's right. Some product can even do look ups. Now you may be familiar with using V lock up. We have the match function or even better index and match together for two way look ups. We're now going to see some product do it, using its capability to test multiple conditions as a clever trick to perform lookups. So what I've got here is in Selby won a country country Singapore. I've got countries income A and then a month, such as March in D one and months, A long Road three. And I would like to perform at the look up across column end row for that country that month and return the South figure in F one. So let me zoom in to F one so you guys can see there the formula and it stopped the some product and the first are Ray. Let's put opening back in for the array. It's going to be the bunch of countries India in a fort a 13 Australia on order tested that's equal to the country, written in B one, closed bracket on that array and then an asterisk. So that kind of multiply. Why cold? Which because the way some product finks and you know it works is kind of used is the and logic and I actually that. And let's put opening bracket in. I'm now going to test the array off countries against Select from a free and the weather's ahead up. But I'm gonna go across to, uh, m free there and tested. That is equal to the month mentioned in D one company, March for May close bracket and then I need to return the sound figure. So I'm going to put a comma for that kind of second a right coming in here. And I'm going to highlight the list I four away across in Crete. Nordea's values because it will be looking within their to M 13. This is what have it atop close bracket on some product there. And if I press enter, I have 246 Let me zoom out for one moment. 246 Here we go March, Singapore 246 is the correct answer, and we zoom back in and this is the formula we have so I've got two conditions here. Look at the countries for the one mentioning. Be one look at the months for the one mentioned D one and the asterisks to multiply to, say, look, they must both be correct. Make sure it's both and then off provided Lee Color entire table range. Really to look within as a right to you can see it mentioned the bottom. The first. Raise me, just multiply normal, get a bunch of ones and zeros that have provided the ever one groups and he ever writers ones and zeros we multiplied by. But why not get over here and ultimately returns to value from that cell? What gives the appearance that it's returning to value from its cell where really it's, Ah, it's multiplying, and something is what some product does So such a genius and clever way around this stuff and there's so many more examples that I can't cover in this video will be here all day. So I'm picking five adventurous and unexpected very things that some products can do