Hi, this is Wayne again with a topic “The Excel SUMIFS Function”.
In this article, i’m going to show you how to use the sumifs excel function. This function is just a fantastic function, very useful, very helpful. You can see. I have a simple spreadsheet here that serves as a movie inventory for my dvd and blu-ray collection and let’s say i would like to figure out how much money i’ve spent over the years on dvds on blu-rays or maybe on different genres of movies. So this calls for a sum function and i could do a typical sum function here or use autosum, but that would give me the total of everything.
So what if i just want a list of all the blu-rays and the total cost for them? Well, i could filter the data. I could deselect dvds and just show blu-rays and things like that and then do a sum function just of the blu-rays, but that can get time-consuming and what, if there’s not just two options? What, if there’s five or six like in the case of genres, so what we can do is use a sumifs function. Let me demonstrate here in i3, i’m just going to type in cost of blu-rays and then i’ll tap enter a couple of times and type in cost of dvds. Okay, so right here, i would like the total cost of my blu-ray collection to appear. I would start by typing equals and then use my function. Sumifs left parenthesis and excel shows us what it’s looking for.
It wants the sum range. So what is it that we want it to add up? Well, we want it to add up the cost paid. So i’ll click and drag to get the whole sum range now, if i only have 20 or 30 rows or records to select, that’s a good way to do it. But what if i had a thousand records – and i wanted excel to examine all of them – to see what to add up clicking and dragging, is not going to be a great option. So, instead, what you do is you click on the topmost record that you care about, hold ctrl shift and tap the down arrow on the keyboard. That will automatically highlight the information from the point that you selected all the way down.
Okay, so i’ve got the sum range now put in a comma and now i need to find the criteria range. So i want the criteria range to be this format column, so i’ll click here on blu-ray control shift down. So it looks through all of those searching for what well that’s what i put in next comma. What am i searching for? What is the criteria? Well, it’s blu-rays, so i could either type out blu-ray in quotes or i could simply click here on b2 to show excel what i’m looking for. I’M looking for the word blu-ray.
Let’S just leave it in quotes i’ll put in the right parenthesis and then tap enter on the keyboard, and you see what it did excel searched column b, looking for the word blu-ray and when it found it, it added the cost paid or the amount paid for That blu-ray to all of the other costs of blu-rays from this entire sheet. This is a very powerful time-saving function that we have here. Let’S do the same thing now, but with dvds, i’ll type equals sumifs left parenthesis the sum range. What do i want it to add up? I want it to add up at least some of these numbers. Now i can go back up here to continue my formula, or sometimes it’s easier just to go up here to the formula bar comma.
What’S my criteria range? That’S here i select the topmost record that i want it to search ctrl shift down. It selects everything and then up here in the formula bar comma. What do i want it to find? Well, in this case, i want it to find dvds now this time, instead of putting the word dvd inside of quotation marks, i just want to click on it, just to show you that that is another option, so i can click there put in the right parenthesis. Tap enter 71.. Now i could format this to look like dollars and there we have it it’s summing up the cost paid if the format is blu-ray or in this case dvd now, because the second example is based on this cell b5 watch. What happens if i type in blu-ray there? It updates the amount, because it’s not based on something in quotes. This one is based on whatever is in b5 i’ll change that back to dvd and it changes the results again now there is a reason. It’S called some ifs and not just sum if and that’s because you can do more than one sum if at a time. So let’s look at how to do that.
What if i want to know the total amount spent not just on dvds, but specifically mystery dvds? How would i do that well equals sumifs left parenthesis. I want to add up the cost paid ctrl shift down i’ll go up here to the formula bar comma. The criteria range it’s right here. I want it to search b2 all the way through the bottom, so ctrl shift down, and then i put a comma in the formula bar i’m searching for dvds, i’ll just click here on this b7.
In this case, where the word dvd is – and then i put a comma in so that’s my first if but look because i put a comma excel – is now expecting. Another criteria range criteria, range 2., in addition to dvd, i’m looking for mysteries, so i’ll go up here. Click on f2, in this case ctrl shift down to get the whole column and then back up in the formula bar comma and what is the actual criteria? What is it that we’re looking for we’re looking for mysteries, so, as you can see, this gets fairly complicated if you have more than one criteria in your sumifs formula, so this is all lit up in different colors.
But let’s see if it worked i’ll put a close parenthesis tap enter and i’ve only spent twenty four dollars on mystery. Dvds, in my collection – and you can see that that’s true just by browsing down fifteen dollar mystery dvd plus a nine dollar mystery dvd. So this is just one example of how to use some ifs. There are lots of very powerful uses of some ifs. I hope you found this tutorial to be helpful. If you did please like follow and subscribe and when you do click the bell so you’ll be notified.
When i post another video .