Get to Grips with COUNTIFS and SUMIFS

Name: Get to Grips with COUNTIFS and SUMIFS
Uploaded: 2024-12-17T03:02:45.761Z
Duration: 19 min 44 s
Channel: Data Translates
Description: #trainingDigital #education #educationtech

Data Translates

219 followers

Dec 17, 2024 #trainingDigital #education #educationtech

#trainingDigital #education #educationtech

Hello everyone and welcome back to my channel. In this video, I'm going to show you how you can use SUMIF and COUNTIF, and also SUMIFs and COUNTIFs in Excel. And what all of these different functions do is allow you to do a SUM or a COUNT based on criteria that you specify. Now SUMIF and COUNTIF were the original two functions in Excel. And then at a later time, Microsoft released COUNTIFs and SUMIFs, which basically allow you to deal with multiple pieces of criteria. So in this video, I'm going to show you how all of these functions work so that you know the difference and know which one you need to use in different situations. And I'm going to demonstrate these on a small table of data that represents sales team members working at two different companies. And we're going to do some counting and some summing based on different pieces of criteria. So if that sounds like something that you're interested in, then please keep watching. So before we begin, let's start by understanding the data that we're going to be analyzing. And this is a very small data set. You can see here in column A, I have the sales team members. In column B, I have the company that they work for, I have their job title, and these people are either sales reps or sales managers. And then in column D, I have the total amount of sales that each of these people have generated. And I want to use this data to perform some calculations. If we scroll over and just take a look at what we have over here. Now, this first little table at the top here, this is where we're going to be doing SUMIF and SUMIFs. And you can see the first thing that I want to work out here is the total sales for MicroWorld. So basically, I need this formula to look at my table of data. I need to pick out of the company range, wherever we have the word MicroWorld, and then I want it to sum the corresponding sales value. In the second example, I want to find out the total sales for all sales reps at MicroWorld. So I'm adding in an extra piece of criteria. So I want to say sum the total sales whenever the company equals MicroWorld and the job title equals sales rep. And then in the third example here, I want to sum the total sales for all of the sales reps who work for MicroWorld who have generated more than £7,000 in sales. And we can work all of this out by using SUMIF and SUMIFs. We're going to break down both of these formulas so that you can see the difference between the two, which is going to allow you to know when you need to apply each one. So the first calculation we want to do here is we need to find out the total sales for MicroWorld. Now, in this formula, I have one piece of criteria, and that is MicroWorld. I'm basically saying sum when the company is equal to MicroWorld. That is my one piece of criteria. So if you only have one piece of criteria, then you're going to need to use SUMIF, open bracket. Now, let's take a look at the arguments that we have here. We have range, criteria, and then we have an optional argument, SUMRANGE. So the first thing we need to tell the SUMIF formula is the range that contains our criteria. So what is our criteria in this example? Well, our criteria is MicroWorld. So the range is going to be wherever we're going to find that piece of criteria. So my range is basically the entire company range, comma. Next, we need to tell SUMIF what our piece of criteria is. What is Excel looking for in this range? Well, I wanted to find the word MicroWorld. Now, because I have this listed out in a cell, I can simply select the cell. You could, if you wanted to, hard code the company name in. I wouldn't recommend you do that, but you can do that if you want to. Just remember the text needs to go inside quote marks. Now, I'm not going to do that. I'm going to use the cell reference, F6, comma. So once Excel has found the word MicroWorld in the company range, what do we want it to sum? Well, I want it to sum the total sales. And you can see this says SUMRANGE, so we need to select the entire total sales range, D6 to D17. Let's close our bracket. We are done with our first formula. Hit Enter. There is our answer. Now, because I have a very small data set just here, it's quite simple for me to go through and just visually check to make sure that that calculation is correct. And a little tip here, something I would recommend that you turn on, if you go down to your status bar and right-click your mouse, it's always good to have AVERAGE, COUNT, NUMERICAL COUNT, MINIMUM, MAXIMUM, and SUM turned on. And when I say turned on, make sure you have a tick next to them. What that means is when you select cells, you're going to very quickly be able to see all of these calculations in the status bar at the bottom, which makes doing things like checking your results a lot simpler. So if I want to quickly do a visual check, I'm going to find every time we have the company MicroWorld and let's select the total cell. So I have MicroWorld there and there, there and there. I have it down here and also at the bottom here. So now I have all of those total cells selected wherever the company is MicroWorld. If I look down in my status bar, I can see the AVERAGE, COUNT, NUMERICAL COUNT, MIN, MAX, and SUM. So the SUM value showing in the status bar should match the result of my SUMIF calculation, which I can see that it does. So, so far, we're looking good. So you use SUMIF when you have one piece of criteria. Let's move on to the second example, because here we have two pieces of criteria. Now we want to sum the total sales when the company equals MicroWorld and the job title equals Sales Rep. Now, because we're using two pieces of criteria, we need to use SUMIFs instead. Let's open our bracket. Now, the arguments here are slightly different. The main difference here is that the SUM RANGE argument is the first argument you provide the formula. When you're using SUMIF, it's the last argument you provide. So in this calculation, what is the SUM RANGE? Well, the SUM RANGE is the total sales, comma. We then have Criteria Range 1 as our next argument. And if you take a look at what the following argument is, it is Criteria 1. So my first piece of criteria is going to be MicroWorld. So my Criteria Range is going to be wherever I find that piece of criteria. So Criteria Range is going to be the company range, comma. The criteria, what am I looking up in that range? Well, I'm looking up MicroWorld, cell F7, comma. Look at the next argument, Criteria Range 2. So what is my next piece of criteria? Well, Criteria 2 is Sales Rep. Where am I looking for that? Well, I'm looking for it in the job title range. So that is Criteria Range 2, comma. Criteria 2 is Sales Rep, G7. Let's close our bracket and hit Enter. And that gives us our answer. Let's do another quick visual check just to make sure we're staying on track. I'm looking for MicroWorld and Sales Rep this time. So there's one just there. There is another one just there. And that is it. So if I look down in my status bar and take a look at that sum, 13,340, and that is exactly what we have in our result cell. Let's do this one more time because this time we have three pieces of criteria. So once again, because we have more than one piece of criteria, we're using SUMIFS. Our sum range is the total sales, comma. What is our first criteria range? Well, our first piece of criteria is MicroWorld. We're going to find that in the company range. Criteria 1 is MicroWorld, F8. Criteria Range 2, well, Criteria Range 2 is going to be the job title range. Our criteria is Sales Rep. That's what we're looking up, comma. We're now on to Criteria Range 3. So for this one, I only want to sum if the total sales are greater than 7,000 pounds. So my Criteria Range 3, well, for this one, that is the total sales. And what is my criteria? Well, again, I have it listed out in a cell greater than 7,000. So I can just select the cell. Again, if you don't have this listed out, you could hard code it in. Again, it's not something I recommend. Wherever possible, try and use cell references. But if you wanted to, remember, it needs to go inside quote marks. And you just type in greater than 7,000, like so. And just for kicks, let's just leave that one as it is. Hit Enter. And there we go. So if I look through and take a look at MicroWorld sales reps that are greater than 7,000, that one, no. What about this one? Yes, that one is correct. And that is basically all we have in the list. So just this one, 7139, and that is exactly what we have just there. So that is how you can use SUMIF and SUMIFS. The only real difference between the two is the number of pieces of criteria that you have. If you just have one, you can use SUMIF. If you have multiple, use SUMIFS. So with all that said, let's take a look at how COUNTIF and COUNTIFS work. And this won't surprise you very much, but it works in a very similar way. Except this time, instead of summing, we're actually counting the items in a list. So you can see the first piece of criteria I have here, I want to find the total number of employees at Computech. So I want to count the number of employees. So because I have one piece of criteria, I can use COUNTIF. Here I have two arguments, range and criteria. So what is my criteria? Well, it's going to be Computech. Where am I going to find that? I'm going to find it in the company range. B6 to B17, my criteria is Computech F14. Close the bracket here, enter. I can see that I have six employees who work at Computech. The next example, we want to count the total number of employees at Computech with the job title of Sales Manager. Now I could go through and do this as I have been doing it, but you know me, if you've watched any of my videos, you know that I do love a named range. So let's take a look at how we can utilize named ranges to make this a little bit quicker and easier. So named ranges, if you've never used them before, is just the art of naming a group of cells. Whatever cells you select, you can give them a meaningful name and then refer to that name in the formula, as opposed to selecting the cell range each time. And named ranges are brilliant if you have a very large data set. Mine's quite small here. So named ranges are a little bit unnecessary because it's fairly simple for me to just select these cells. But if I have 20,000 rows, I don't want to be selecting that cell range every single time. It's much easier to use a named range. So let's name all of the columns in this table. So I'm going to select my entire table up to the Formulas tab. And in the Define Names group, we're going to create from selection. And I want to use the labels in the top row as the names for each range. So Sales Team, Company, Job Title, and Total Sales. Let's click on OK. Let's check to make sure they've set correctly by clicking the dropdown in the name box. And you can see those are my named ranges. So if I was to select Job Title, you can see that that now refers to this range of cells. So now I can use these names in my formulas. So this time we're going to use COUNTIFS because we have multiple pieces of criteria. Criteria Range 1. Well, my first piece of criteria is Computex. So I want to look this up in the Company range. So instead of selecting the cell range, I can just start to type in the name of the range, which is Company. You can see IntelliSense underneath has recognized that named range. And I can simply just press the Tab key to select it. What is my criteria? Well, my criteria is Computex cell F15. What is my criteria range 2? Well, it's the Job Title range. Once again, I can just start to type in Job Title and you can see IntelliSense picks it up. I can press Tab to select. My second piece of criteria is Sales Manager G15. Close the bracket. Hit Enter. And I can now see that I have three employees at Computex with the Job Title of Sales Manager. And if you take a closer look at this formula in the formula bar, you can see that for anybody looking at this spreadsheet, this is a lot easier for them to read and understand because we're using named ranges rather than cell references. Let's finish off by doing our final example with three pieces of criteria. We're doing COUNTIFS again. Criteria range number one is again going to be the Company. Now, in the previous example, I started to type in the name of my range in order to select it. But what if I haven't looked at this spreadsheet for six months or a year? Am I going to remember the names of these ranges? Probably not. So how can we get around that? Well, something that you could do is and that's going to pull up any named ranges that you have in your workbook. So from here, I can see, okay, there's Company. That's the one that I want. Let's click on OK. And it's going to insert that named range. My criteria is Computex. Criteria range two is the Job Title. Again, I can press F3 and select Job Title, comma. Criteria range number two is Sales Manager, comma. Criteria range three. Let's press F3 again. This time we're looking for sales less than 8,000. So the range that I'm looking this up in is the total sales, comma. And I'm looking for less than 8,000, H16. Remember again, if you really wanted to, you could hard code this in and type in less than 8,000. Remember, if you do hard code numbers into your formulas, that means that they're not going to dynamically update. So if I hard code 8,000 in and then in a month or so's time, change our criteria to less than 9,000, that formula isn't going to update because I haven't referenced the cell. I've hard coded 8,000 into the formula. So that is why it's always best where you can to use those cell references instead. Now, just to finish off, I'm going to throw in a little bonus here and show you AVERAGEIF and AVERAGEIFS as well. And if you've mastered SUMIFS and COUNTIFS, then you're not going to have a problem with AVERAGEIFS. And as you can probably guess, these functions are going to allow you to calculate the average as opposed to the sum and the count. I'm going to show you how to do this in a different way yet again. So this time, I want to find the average of total sales that Computech has generated. But this time, I'm going to use the functions dialog box. Now, this may or may not be something that you like to use. I know a lot of people who are reasonably new to creating formulas find this quite helpful. I myself prefer to type the formula directly into the cell and just use the screen tips to see the arguments. But let's take a look at how we can use the functions dialog box. Now, to access it, you just need to click on the FX icon just here. So where it says Insert Function next to the formula bar. Let's click. And you can basically search through the entire library of functions from here. So I'm looking for AVERAGEIF. So I'm going to type that in and click on OK. And you can see it's brought up AVERAGEIF and AVERAGEIFS. Now, the first one that we're doing only has one piece of criteria. So it's going to be an AVERAGEIF. Let's double click to select. And basically what you get are the arguments listed out with these little input boxes as opposed to just underneath in the cell. But it works in exactly the same way. So the first thing I need to define here is the range. So the range is going to be where I'm going to find my piece of criteria. My criteria is Computex. So the range is going to be the company range. The criteria is going to be what we have in F20. And the average range, well, I'm looking for the average of the total sales. So that is going to be this range just here. And you can see that because I've named these ranges, when I select them, it's picking up the name as opposed to the cell references. Let's click on OK. And there is the answer. You can see I have some weird formatting going on here. I've got lots and lots of decimal places showing. Let's change that by making it pounds. And that looks a little bit better. Let's do the final example using Functions Dialog. This time I want to use Average Ifs. So I'm going to search for Average Ifs and click on Go. There it is. Let's click on OK. So my average range is the total sales range. That's what I'm looking for the average of. My criteria range 1 is going to be where I find that first piece of criteria. The first piece of criteria is the company. So we're going to go for the company range. Criteria 1 is Computech. Then I have Criteria Range 2, which is going to be the job title. Criteria 2, G21. Let's click on OK. There we have our result. Let's once again change this to pounds. And that is how you can use Average If and Average Ifs using the Functions Dialog box. Remember that Functions Dialog box is available for all functions in Excel. So if you definitely found that a little bit easier as opposed to just typing the formula into the cell, I would recommend that you use that each time you do a formula. That's it for now, guys. I hope you found that super useful. Don't forget, guys, if you enjoyed this video, please smash that like button. Consider subscribing to my channel and hit that bell icon so that you receive notifications when I upload a new video. That's it for now, guys. I will see you in the next video.