Hi everyone, Kevin here. Today I want to show you how you can use the SUMIF function in Microsoft Excel. So first off, what is SUMIF? Well, let’s say I have all of these photos of delicious looking treats, but I want to know how many chocolate chip cookies do we have out of all of these photos. Well, only two of the photos have chocolate chip cookies, and each one has two, so the sum of that is 4 chocolate chip cookies and that’s basically the SUMIF function. Please subscribe. All right. Oh, and yeah, let’s check this out in Microsoft Excel. Here I am in Excel and if you want to follow along, I’ve included a sample workbook down below in the description. Here I want to know how many chocolate chip cookies we sold throughout the world. Here I can see in the United States we sold 28. In India, we sold the 75, and if I scroll down just a little bit more, here in the Philippines we sold 55. With all these cells selected, down here in the status bar, I can see that we sold 158 chocolate chip cookies throughout the world. Now I could do that manually, but that takes a bit of time and instead I can rely on Excel by using the SUMIF function. Let’s click up here into this cell. And up on the top bar, let’s click on insert function. This opens up the insert function helper and we’re going to use a function called SUMIF. Type in SUMIF and then click on go, let’s click on the first results here and then click on OK. This opens up the function arguments and this is by far my favorite way of writing functions because it tells you exactly what you need to enter in. If you’re ever unsure of well what’s the range, well, you could just look down below and it’ll give you a hint as to what that is. Now here the range is the list where we want to look for chocolate chip. So here I’ll select this column and next it asks me for the criteria. Now remember we selected this list, and we want to look for chocolate chip within this list. I can simply select this cell or as an alternative I can type in chocolate chip. Now, once it finds chocolate chip in this list, we want to sum up the number of cookies sold. That’s this column right here. So, last it asks me for the sum range. Here I’ll select this column and I can already see that it gives me 158 back. That’s the same result that we got when we just selected each individual cell. Next, let’s click on OK. And check that out, we only sold a measly 158 chocolate chip cookies, our flagship cookie, around the world. Please don’t share that with our investors. Next, I want to show you how you can use the wild card character (*) in the SUMIF function. So, let’s say I want to know how many cookies we sold in countries ending in an s. I don’t know, management asks these random questions, so let’s figure this out. Here I’ll copy this function down below and then once again, let’s click into the function helper, and here we want to now look for countries that end in an s, so we’ll change the range to country and here for the criteria, let’s enter the wild card character or the asterisks and then enter an s. So, what is the asterisk? Well, it’s any number of characters before the s, so in this list, that would include the United States, and it would include Philippines, but not India because it does not end in an s. And down below the sum range stays the same. Here I’ll click on OK, and I see that we sold 802 cookies in countries ending in an s. Here I can highlight these cells for the United States and the Philippines, and here I can confirm that’s correct. You can also use the question mark (?) wild card character. So, what’s the difference? Well, the asterisk (*) will give you any number of characters, while the question mark (?) will just look at one specific character. So, you could use both of those. You might have noticed when we inserted the SUMIF function there was another function called SUMIF but this one included an S at the end. So, what’s the difference? Well, this one could do everything that SUMIF can do, plus you can include additional criteria. Let’s start by seeing how we can get this same exact result using SUMIFS. Here I will click into this cell and once again, just like we’ve been doing before let’s click on insert function and this time let’s type in SUMIF. But instead of choosing the version without the S, let’s choose the one with the S and then let’s click on OK. This opens up the function arguments and the arguments that it asks for look a little bit different, but it works the same way. First it asks me for the sum range, and this is what I want to sum up. Now once again, I want to know the cookies sold, so I will select this column. For the criteria range, once again, I want to know how many cookies or how many chocolate chip cookies did we sell throughout the world. I’ll select this column and then for the criteria, I’ll type in chocolate chip. Now that I have all of this entered in, if I click away, we’ll see that it gives me 158. I’ll click on OK and that gave us the exact same result as some SUMIF. So, you could just use SUMIFS instead of SUMIF but not only can you look at just one criteria, but you can also look for additional criteria. Here, for example, you can use SUMIF and S to look at multiple columns. So, let’s say that I want to know how many chocolate chip cookies did we sell in the United States. So, I’m looking at two different criteria. I want the country to be United States and I want the cookie name to be chocolate chip. Now we could see that it’s 28, but we could use SUMIFS to to tell us that. Here, once again, let’s click on insert function and let’s click on SUMIFS. Here it asks me for the sum range. Once again, I want to add up the number of cookies. Here criteria range one. First, let’s look whether it’s the United States. I will select this list and for the criteria I will select United States, but here now you’ll see that I can enter another criteria. Here, I’ll select the cookie name and for the criteria, I’ll select chocolate chip and then I’ll click on OK, and here we can see that we sold 28 chocolate chip cookies in the United States, so this is now based on two different criteria. With this example, the criteria happened to be in two separate columns, but what if I have two separate criteria in this same column? How does that work? So, what if I want to know how many chocolate chip or oatmeal raisin cookies we sold in the United States? Let’s test this out. Let’s once again insert a function and let’s select SUMIFS. For the sum range, once again, let’s select the cookies sold. Criteria range one, let’s select the country and here I’ll select the United States. For criteria range 2, here I’ll select the cookie name and for the criteria, here I can select both chocolate chip and oatmeal raisin. Here you see that it inserts this range A2:A3. Then I can click on OK and here I get an array back. Here it tells me that we sold 28 chocolate chip cookies in the United States, and we sold 32 oatmeal raisin in the United States. But I get these back as separate rows. I just want to know the total across chocolate chip and oatmeal raisin in the United States. Up here in the function, here I can insert SUM, put parentheses around SUMIFS, and then another parentheses to close it, then I could hit enter and here I see that we sold 60, and here if I highlight these two cells, I can confirm that we sold 60. Now what if you want to know how many chocolate chip or sugar cookies did we sell in the United States? Does that work the same way? Well, let’s test this out. Here I’ll click on insert function. Let’s select SUMIFS again. I’ll select the sum range for cookies sold. For the criteria, let’s once again select the United States, and over here for criteria range 2, I’ll select the cookie name column. And for criteria 2, let me select chocolate chip, press control and then select sugar cookie. So, I have these two cells selected. When I click on OK, it tells me that there’s an error, so that doesn’t work. So, how can I enter nonadjacent cells as the criteria? Well, obviously entering in A2 or A4 isn’t going to work, so let’s once again open up the function helper. So, over here I can answer the curly bracket. Basically, I want to pass in an array of multiple values as the criteria. Here I can type in chocolate chip. Here I’ll close the quotes and then I could insert a semi colon. That’s how you separate the different items within an array, and here I can type in sugar cookie. Then I’ll insert the quote again, and then I’ll close the curly bracket. Then I can click on OK and here I get 28 for chocolate chip and I get 52 for sugar cookie and once again if you just want the sum, here I can enter the sum function, parentheses and then close it off at the end and that tells me that we sold 80 chocolate chip or sugar cookie. Exactly what it shows right up here. Now, entering in an array into the function isn’t really the easiest thing to do, so instead here let’s say I want to know chocolate chip, sugar cookie, or birthday cake. I can copy these three values and here I’ll just paste them over on the side. Here within the function, let me remove the array that I passed in, let’s remove all of that. And instead let’s say I want to look up these three different values. I could select this range right here and then I could press enter and here I see that we sold 124 and let’s just confirm that. I’ll select chocolate chip, sugar cookie, and birthday cake. Here I see 124 and it gave me 124 right up above. So that’s an easy way that you can sum up nonadjacent criteria. You are now an expert in using SUMIF and also SUMIFS and you know how to look at multiple criteria in different columns or even on different rows. And now that you know SUMIF, you can also use AVERAGEIF, AVERAGEIFS, COUNTIF, COUNTIFS, MINIFS, and also MAXIFS. They all work the exact same way and with all of these, you can use the wild card symbols, you can enter in multiple criteria in different columns and multiple criteria in different rows. All right, well if you ever forget what the SUMIF function does, just remember, it’ll give you the sum of just the chocolate chip cookies. Mmmm, I should bake some chocolate chip cookies tonight. To watch more videos like this one, please consider subscribing. You can also check out a playlist of all of my Excel videos down below in the description. I’ll see you in the next one.