SUMIFS is a function I use regularly in my modelling work, particularly if I want to summarise monthly or quarterly data into years. Which is actually one of the things we'll look at in this video. It's also used a lot in output sheets, KPI packs, and summaries - anywhere where we want to roll up more granular data into a higher-level. I would definitely say put this in your Top 10 or your Top 5 functions - it is super useful!
In this video we'll look at how we apply it in a variety of ways to quite a big dataset. Including defining multiple criteria in our summaries. But I'll warn you now, one of the biggest challenges in coding your SUMIFS is getting your 'anchoring' right. We'll see what that means a bit later.
We'll start by introducing the data. We've got different types of clothing and the Category that they fit into. We've got the Gender - Male and Female. And then a Price point. Over here we've got Quantity Sold in Quarters - starting in Q1 2015 going to Q4 2018. We also have these quantities summarised by Season - Autumn/Winter (AW) and Spring/Summer (SS).
Question 1 asks us to "Summarise the quarterly sales data annually." We'll do that in these blue cells here. I'll start typing out a SUMIFS function and then explain it. We start with =SUMIFS. Note, there is a SUMIF option here. With a SUMIF you can put 1 criteria in it. SUMIFS you can put multiple criteria. I recommend always just using SUMIFS, as anything you can do with the SUMIF, you can do with SUMIFS. And if you just use one type of function in your work then it's more consistent.
So we'll go with SUMIFS. We start with a Sum Range - this is where we'll actually be summing data. In this first question I'll be selecting this row. With a SUMIFS, the Sum Range must be either 1 row or 1 column. You can't have several columns and rows. So it's got to be kind of 1-dimensional. In this case we'll select the top row there and anchor the columns, as we'll be dragging this to the right.
The term âanchoringâ refers to when we place a $ sign in front of the column letter and/or row number. If a column or row has been anchored then when we copy the formula across to adjacent cells, the column or row will stay frozen and not shift about. We can either manually place $ signs within the formula, or we can press F4 once to anchor columns and rows, twice to anchor just rows, or three times to anchor just columns. Give it a go!
Next we need to enter a Criteria Range - you can see this says Criteria Range 1 - so if we want to enter multiple criteria then we'll have Criteria Range 1 and then a criteria, Criteria Range 2 etc. We'll start with the Criteria Range 1, and in this first example we'll only be using one criteria and that'll be these years at the top. So I'll double anchor that and then finally we need the Criteria 1 which will be our Year 2015 here. I'll anchor just the row. I'll close the bracket, press ENTER and explain what's going on.
The Criteria Range - the years across here - Excel will look for the criteria 2015 in this range and where that criteria is met it will sum the Sum Range. So in this case it's just summing the first 4 cells. I'll press ENTER and copy that across and down. Here the criteria is 2016, and so it's summing these cells below here - the 218 to the 261. If we check that we can see that adds up to 1,021 which is the result we get. Similarly, because we didn't anchor the rows of the Sum Range here, as we've dragged down it's then dragged the Sum Range down as well.
So now we'll sum up 2016 in this second row. So it'll be these cells here - 244 to the 385 - and we get 998 which is the result we get here. I'll continue and copy this down. We can check that we summarised all the data correctly by selecting it all and we see the sum down here is 236,070. If I select all that we get the same figure, so that has worked correctly.
So that is how we summarise months or quarters - in this case quarters - into years. If you do find this video useful then please do Like, Subscribe or leave a Comment. I'll know they're working and I'll keep making more. Or if you have any feedback or suggestions for future videos, I'm always happy to hear. Thank you.
Question 2 asks "Summarise annual and quarterly quantity of Shoes sold by Gender in the blue cells." So this will be the annual summary and this will be the quarterly summary. These are the criteria - so it's Shoes i.e. Shoes here and here, and then the Gender, so these are the Male Shoes and these the Female Shoes. I'll start typing this out. One thing to note is that as I said earlier, because we can only do a row or a column, this annual summary this time we can't select this data and that's because we would need criteria down the left hand side - so Shoes and the Male and Female - and then criteria at the top, which we can't do - its got to be 1-dimensional. If we did want to do this by selecting that whole range of data, then we'd need to use a SUMPRODUCT. I'll do a quick demo later in this video. Or for a more extensive look check out my SUMPRODUCT video.
So here our Sum Range will be what we've just produced in Question 1. So it'll be 2015. So it removes the need for this 2015 to be a criteria. The only criteria we'll need are the Category and the Gender. I'll anchor the rows here and leave the columns unanchored as we'll be dragging this to the right. I'll now select the Category here. I'll double anchor that. I'll select Shoes here as Criteria 1 and I'll anchor just the column. I just want to show you - if I was to copy and paste this now, along the top we'd get 43,461. This is essentially - if we go around and add up all the Shoes for both Genders we get 43,461. Then we've got exactly the same repeated below because we're just going through the same sum - it's just summing all this for all the Shoes, so we get exactly the same result in both. But now we want to add in a second criteria. So here we'll enter Criteria Range 2 and this time that will be the Gender column here. I'll double anchor that and then I'll pick Male here and anchor just the column. What we find now is when I paste this our result again is 43,461 but now the top row is just summing Male Shoes.
So 26,525 should equal these cells - 26,525 - and then Female Shoes is 16,936 which equals this bottom row. We can now take this and copy that formula across to the quarters and you'll see because we've left these columns unanchored in the Sum Range - this being the Sum Range - the Sum Range just moves across and our Criteria Range over here just stays the same.
So that's Question 2 - we've summarised using 2 criteria - Category (Shoes) and Gender (Male & Female). I'll group rows with Shift + Alt + Right Arrow so we can move onto the next questions and see the data above.
Question 3 asks "How many Female items priced at 269 or over are sold each year?" Again we're going to need 2 criteria and that's going to be Female but then also the Price - anything that's 269 and above and which is Female, we're going to be summarising the Quantity Sold. To do that we'll enter SUMIFS and the 1st column will be our Sum Range, so we need to anchor the rows and then the Gender column will be our Criteria 1 and we'll anchor both rows and columns and the criteria is Female and we'll anchor the column.
Next we need to pick the Price column as our Criteria 2 range - I'll double anchor that. Now, when we select this 269 as our criteria, we want it to be greater than or equals to this. We can't just put in >= and then that cell - we'll get some kind of error... Excel doesn't like that. What we actually need to do is put the >= sign into quotation marks and use an ampersand ('&' sign) to join it to the cell reference so that the SUMIFS function can recognise text being combined with a cell reference. If I just anchor the H, press ENTER, we now get the result. Essentially it will be the items that are Female and are 269 and over, which will be these... 36,607. So that is Question 3. Again I'm just going to group that so we can see Question 4.
Question 4 asks "How many Male Trench Coats were sold in Autumn/Winter quarters across 2015-2018?" This is going to be our criteria - A/W quarters, and that criteria is along the top. As I said earlier, because Trench Coat and Male could be criteria, we can't have criteria down this way and across the top because our Sum Range has got to be either the whole column or the whole row.
So in this case we actually have to select...we know Male Trench Coats is across the top...we'd have to select as the Sum Range this top row and then we'd select the Seasons as our Criteria 1 and then this A/W here as our Criteria, and we get the result.
Now, if we did want to sum Quantity here using criteria across the top and down the side we'd actually have to use an alternative to SUMIFS - we'd have to use SUMPRODUCT. So I'm just going to demo this - it's actually a really versatile and useful function. It's very powerful. It can also be a bit confusing and not necessarily very transparent, but I've got quite an extensive video which shows the different applications of SUMPRODUCT.
So let's say we wanted to add in a criteria which was just the Item and we wanted to keep this Season criteria. I'll make a List. You can see this is Data Validation. If you want to see more Data Validation then I've got a Data Validation video which is worth checking out. I'll select this as the Source. Let's start off just by selecting the Trench Coat.
Here I'll enter a SUMPRODUCT I'll select the whole Quantity range here, I'll close the bracket, put a multiplication sign, open a bracket and then I'll select the Seasons across the top as a criteria, and it's going to equal this cell and then multiplication sign, another open bracket, and I'll select the Items range here and equals this 2nd criteria, and then double close the brackets, and we get 2,035.
I can now actually select Denim and it'll now select this Denim across here for the A/Ws. So I'll sum this up to demonstrate that it's working, and our result is 2,321 which is the result we get here. So I can change any of these and then I could put in S/S and so we've got quite good little summary calculation there.
That was SUMPRODUCT, and before that SUMIFS. If you like what you see, check out some of my other videos. Like I say, the SUMPRODUCT video, it's very extensive and there's lots of different ways we can use it. SUMIFS itself actually creeps into a lot of my different videos - I try to mix functions because that's what you'd have to do in analysis and modelling. Guys, thank you so much for watching! I hope you thought that video was awesome and that you check out some more. Bye!
