In this Screencast, I'm going to cover some more advanced logical functions in Excel. I'll be working on this cookies file over here. I've added in a column here for sales. That's just the product of quantity and price, and we're going to do a couple things. The first thing we're going to do is we're going to tally the number of sales for each of the unique sellers. Now here in column A, we've got repeats. So first, I'm going to show you two ways in which we can eliminate duplicates. So I'm going to go ahead and copy over here. I'm going to just paste and then what I'm going to do is go up here to the Data tab and up here, and you'll learn more about this in week 4. Go up here and I'm going to remove duplicates and we're going to select Column H. And what that does is it found six duplicate values, five unique values remain. If we want it to sort that alphabetically, I could go in here, I could go up to the Home tab and I can sort and filter, sort A to Z. So that's one way we can do this. There's also another way to do this whole thing. If you have Office 365, you can go ahead and you can type in equals. And I'm going to do unique, so there's a unique function of an array so I can click on that and that will give me the unique ones in there. So it automatically removes duplicates also in front of this, if you've got Office 365, you can put sort and it's going to automatically sort those in alphabetical order. So either way you want to do this, is fine. Please keep in mind that if you are preparing a spreadsheet for somebody who doesn't have Office 365, then you cannot use the sort and unique functions without giving them major headaches. So I'm going to just move this down a little bit. I've added in these two labels here. We're going to calculate the number of sales that's going to be the total number of sales for Charlie, Dana, Ella, Ezra, and Max. And then we're going to sum the total sales here. To do this, there is a countif function in Excel. This first argument is the range that we're going to count. So we're going to count this range here, and I'm going to press the F4 key there to make that absolute and the criteria. We're going to look through the range there A2 to A12 and we're going to to look for Charlie here. So I'm making that a relative reference. So now, it's going to do that and Charlie has made three different sales there because I had the absolute reference there on the range and a relative reference on the person to the left, I can just drag this down. And that's a good way that we can determine the number of sales of each of our sellers. We can also calculate total sales, so I can type in the SUMIF function. The first argument is the range that we're going to be looking for the criteria, the second argument in. So I'm going to press F4, the criteria is I'm looking for Charlie and that's going to be relative and then the sum range is if it's different from the first range which obviously it will be here because we're not summing names or summing sales. So I'm going to put that in, I'm going to make that absolute reference and then I can press Enter, that means that I have had $62 of sales. I'm going to go ahead and make that accounting up here. And then the way that I typed in my formula, I can drag that down. So this tells us then the total sales and the number of sales of each of the sellers. Maybe we want to put together a cookie summary and again, I can go over here to the cookies and I can copy and I can just paste. And then I can go up here to the Data tab and I can remove duplicates, select Column H there, 8 duplicate values were found and remove, three unique values remain. So those are the three different cookies. Let's make this a little bit bigger. Let's make this bold and now, I'm going to determine how many boxes of each of these were purchased in some. So I'm going to do the sumif and the range I'm looking over, I'm looking for the match of the cookies in Column C, going to go ahead and make that absolute, the criteria is it has to be in this first row, Choco Chip. And then the sum range I'm looking over. I'm not looking at the total sales, I'm looking at the total quantity. So I'm going to be doing that, make that F4 and then I can press enter and we can drag that down. And those are the numbers of boxes of each of those that have been sold. Now what if I wanted to determine two simultaneous if statements, we can use a new function. So this is new only in Excel 2019 and Office 365, there's a sumifs function. So I wanted to know maybe transaction, I can type in the seller and the buyer and I want to sum the total sales. If the seller is Charlie and if the buyer is Leila, so there's two ifs they have to be going on there in order for us to sum, and that means we have to use this sumifs function and again, this is new. So the sum range I'm going to sum the total sales, make that absolute. The first criteria, the range of the criteria. So I'm looking over the seller and the first criteria is that the seller is Charlie, so I can click on cell M5. The second criteria range is that the buyer, so we're looking over the buyer. I'm going to make that absolutely even though I don't have have to, and the second criteria is that the buyer is Leila. If you wanted to, you could do more matches, but this just does those two and I can press enter, and this tells me then that for Charlie selling to Leila, there's been a total of $20 of sales. Let's just take a look at another one, maybe Charlie to Sally. There have been two sales over here, the first one and one of the last ones. And so we can up, 7 and 35, so it's summing two different criteria, seller and buyer. The next example is going to be in this file Boulder Climate Data, starting January 1st, 2017 going to sometime in August of that year. We have the high, the low, and the precipitation. Now, the interesting thing is that occasionally in the precipitation column, you will see 9999. So we see them quite often. That means that the measurement was not a good one or it wasn't made. This certainly does not mean that on January 9th, there were 9,999 inches of precipitation. The instruments do this because it's easy to filter. I'll show you in week 4 a good easy way to filter for things like this to remove 9999. But today I wanted to show you how we can use the maxif and countif, and sumif functions. The first one, we're going to take the average. Now, if I just did the average of my precipitation, so I click there Ctrl+Shift down. So all the way down to August 31st, then if I press enter, we're seeing that the average is well over 1,000 inches of precipitation each day. Now, obviously that's not correct because of these 9999s, so I can add in there's an averageif function that finds the average given a constraint. So we have our range but then the criteria is the second argument and this goes in quotation, so I can say not equal to so that's what the less than greater than 9999 and quotation. And when I do that, it's going to average everything that's not a 9999, and that's going to be our average precipitation everyday. There is no maxif function, but there is, you can take the max of an if. Now before we do this, let me just show you what happens if we have a array or a vector of different items. So I just put in a simple vector here. If I do the if like this, if I say if this vector is not equal to 9999, then what I'm going to do is I'm going to leave it as the number comma if that's false, I'm going to put empty. So basically what this does is it replaces everything in there, that's not a 9999. Now the max of that, I could do them the max of the remaining elements. We filtered out the 9999 and now up here, I can use the maxif Ctlr+Shift down and this is where I'm going to put my less than equal to 9999. And if that's true, then we're going to just put in the precipitation, if that's false though, we're going to put in empty quotations. And then I need to close my two parentheses and I press enter, and therefore if we don't have a 9999, we're going to calculate the max. By the way, if you do have older versions of Excel, then you're going to when you do this because the if, if we do that on an array that's actually an array function. And so you're going to have to Ctrl+Shift+Enter. Finally, the number of erroneous measurements, we can do the countif so, we're going to count this range. But only if it's equal to 9999, and then I can press Enter. So there are 35 measurements where we got 9999. So hopefully this screencast gave you a better idea of how to use the advanced logical functions in Excel. Thanks for watching.
