So here we're gonna look at a collection of functions called the SUMIF or SUMIFs functions. They're split into two groups, the singular version, so SUMIFs, COUNTIF, AVERAGEIF, these are functions that all exist. there are also SUMIFs, COUNTIFs, AVERAGEIFs.

Yeah. So there are two groups. We'll start with the simpler version, so the singular use and then we'll move on to the more complicated use, the plural version. Okay, so I've got some simple questions here. We've got our same data set of people and sales of products and the question is how many transactions has Betsy made? So obviously each row relates to a single transaction because it gets a transaction number. So we should be able to use then the COUNTIF function to calculate how many transactions Betsy has made. Now to make my life easier, I'm going to just type in Betsy in the sheet here so that I don't actually have to hard code it into the formula. This will be handy if I ever want to change this to Ashley's questions on these as well. Okay, so I'm just gonna put that in the sheet. Next thing is I'm gonna come down to where we've got our first question then. So we're gonna say equals COUNTIF, Tab key to complete. And you'll notice there are two arguments. The first argument is the range and the second argument is the criteria. So the range is clearly gonna be the ones where I'm trying to match the criteria. So I'm gonna come down to the column with the names in. Control+shift down to select the column, comma, and then up arrow key to go and select Betsy as my criteria. Close the bracket, press enter. And there we go. We get 217 as my number of transactions for Betsy. So COUNTIF singular use.

Okay, let's move on. Next question says, what is Betsy's total sales? So we've got the column with a sales amount but it's got a different column to the one we're actually doing the calculation on. So let's see how SUMIF is different to COUNTIF. So, let's start it off equals SUMIF, Tab key to complete. Three arguments this time, two compulsory ones and an optional argument. So you'll notice the last argument is called the sum range. So that's the thing that I'm doing the calculation on. So the first two components are exactly the same as the COUNTIF. It's the thing that I want to do the criteria on first. So that's our names 'cause we're still wanting Betsy. The criteria is still gonna be the cell in E3, which has got Betsy in. And then comma, my sum range is the one that I actually want to do the calculation on. So that is the sales amount. So then let's go and select all of that. Close the brackets, press enter. 28561.5. So that is my total sales for Betsy. The final question gets a little bit more complicated. How many units of mascara has Betsy sold? Now as soon as we get to the situation where we've got more than one criteria that we want to find in the question. So the two bits of criteria. I'll read that question again. How many units, so that's the thing I'm doing the calculation on, of mascara, filter, has Betsy, second filter, sold.

So I've already worked out that there are two things that I need to filter on here. There are two bits of criteria. So I must be wanting something more complicated than a SUMIF and that's where the SUMIFs function, plural, comes in. So like we did with Betsy I'm just gonna stick mascara up here so that I don't actually have to hard code it into the formula. And we're gonna start using our SUMIFs function. So equals SUMIFs, open a bracket. The first argument is the sum range. So notice this is different to my SUMIF in the order the criteria, the order the arguments of my function are wanting. So sum range first. So let's come down and it's asking for the number of units. So that's that column and then the next arguments come in pairs. So you've got criteria range one and then criteria one and then criteria range two, criteria two. So they come in pairs and you can have as many pairs as you like. So I'm gonna do comma and then the criteria range one is the name of the person. Yep. So that's gonna be the first thing. The criteria one is gonna be where I've typed Betsy, comma.

Criteria range two is gonna be the product column, comma.

And the criteria two is going to be mascara. Close the bracket 'cause that's all the information I need. Hit enter. 1,582. So there is my SUMIFs function. Now we've looked at this with a COUNTIF, a SUMIF and a SUMIFs.

But do remember that these functions behave exactly the same way. So COUNTIFs has exactly the same set of arguments. AVERAGEIFs has exactly the same set of arguments. So once you know how to work a SUMIFS function you'll know how to do a COUNTIFs or an AVERAGEIFs version of the same formula as well.

Worth mentioning that obviously if I want to see how Ashley compares with any of these things, because of the way I set up the formula, the only thing that I need to do here is change the item here, Ashley and all of my figures will automatically update.

Okay, so that's a bit about SUMIF and SUMIFs functions.