SUM & Punch-through

Masters the SUM function and save time with punch-throughs!

SUM is just about the most widely used function in Excel. But it doesn't mean there's nothing to learn. This video looks at the basics of SUM - how we quickly add/select individual cells, multiple cells and ranges of cells, and shortcuts to insert SUM faster.

In the second half of the video we take a look and evaluate the Pros & Cons of a technique called a "Punch-through", which allows us to sum the same cell across many worksheets, often saving us a lot of time.

Along the way we even touch on SUMIFS, one of my Top 5 functions, and how we can sum much more quickly with it, using criteria.

Transcript

Do you have lots of cells to add together and are sick of using the addition sign? Perhaps you want to quickly sum the same cell across many worksheets? We’ll look at one of the most frequently used Excel functions – SUM. How we can add multiple cells or continuous ranges of cells. And how we can use a “Punch through” to rapidly sum across many worksheets. We’ll also demo the SUMIFS function that helps us sum even faster it’s one of my Top 5 functions, and will save you a LOT of time. So stay tuned!

In this example we're going to be looking at a few of the ways that we can use the SUM function. Before I do that I'll introduce the data - this is the Quantity Sold by quarter of different items of clothing. In this first column we've got the Item and we've got the Category - so that's whether it's jackets, coats, shirts etc. We've got the Gender - so whether it's Male or Female. And then we've got the Price point. These categorisations we're not actually too interested in for this exercise - it's more the data over here.

So we've got Quantity Sold by quarter from 2015, for 4 years, to the end of 2018. To the left we've got these blue cells where we'll be completing our calculations and we'll be summarising this quarterly data by years and then by season - Autumn/Winter, Spring/Summer. We can see that the data is categorised by year, quarter and then the actual season here.

So before we use the SUM function I'll show you how we would normally add cells the basic way. We'd use the '=' sign and we'd select the 1st cell, press the '+' sign, select the 2nd cell, '+' sign etc. until we've selected all 4 quarters. ENTER. It's 869. I'd press Ctrl + C and hold down Shift, then press the down arrow, then press Ctrl + V to paste. Then I always check that the calculation I've done matches the data. So down here we have 61,236 and we'll check that to these cells - 61,236 - so this has worked correctly.

Now, if we were to use the SUM function we could do something very similar. I'll delete that data. We'd use the SUM function. We'd type =SUM. We can see the function come up there. Open the bracket. And then we could select each of those cells in the same way. So you'd select the 1st one, hold down Ctrl and then select each of the cells, close the bracket, and we get the 869 again.

Now, with the SUM function we can actually just select a continuous range of cells. So to do that I'll press =SUM, open brackets. Now I can just select these with the mouse, or hold down Shift and then press Right arrow, close the brackets, and press ENTER. We get that 869. If I copy that down we'll get the same result as we saw earlier. If I want to complete this for the rest of the years I can do a similar method. I could press '=' and select these. Or alternatively I could take this, press Ctrl + C, Ctrl + V to copy.

Now, if I step into the formula we can see the range highlighted here and I can actually just shift that over. So if I just do that. I'm taking 2017 and then doing the same for 2018. We've selected all the cells for the first item of clothing. I'll press Ctrl + C, hold down Shift + Ctrl + Down arrow, which jumps me to the bottom and then press Ctrl + V to paste. Again, I want to check the sum of all this - that comes down to 236,070. And I want to do the same, hold down Ctrl + Shift + Down arrow, then Right arrow to select that whole range - 236,070 so that's worked correctly.

Now, you might note that we've got these green little boxes this is Excel telling us "the formula in this cell refers to a range that has additional numbers adjacent to it." What this basically means is, if I step into that, it's just saying that this is not summing cells either side. We can actually get rid of this (I'll just press Esc there) - if I select all those cells and then I press this I can actually go to Ignore Error and it actually removes them.

Next we'll look at how we would sum the seasons. Now you'll note, if I want to sum all the A/W quantities they're not continuous, but some of them are. So similarly to the years I can actually select this first one hold down Ctrl, then I can drag over these cells, keeping Ctrl all the time, then drag over the rest of the A/W, navigate across, and then pick them all up, close the brackets and then we can see some of the cells are just one individual cell and then some are a range. If I press ENTER and then I'll do similar for the S/S season. I'll select all these. We should now have all the data selected. I've pressed Ctrl + C, hold Shift + Down arrow, then Ctrl + V to paste. Again we can check that's the 236,070 we saw earlier.

Finally, we'll sum actually each of these columns. We can do that with this shortcut. Normally we would put =SUM and then I go up, Ctrl + Shift + Up arrow, close the bracket and that would sum the entire column. There's a shortcut we can use here, we can just press Alt + =. It selects the whole range, we press ENTER now press Ctrl + C, Shift + Right arrow, Ctrl + V, and then I'll copy this all the way over and we have summed all our columns. And that's basically the SUM function!

Before we move on to another application of the SUM function - the "Punch-through" - I'm going to show you how we could sum these cells even faster using a different function - it's called the SUMIFS function and I've got a separate video on this if you want to see more applications of it, so I'll just give you a quick demo. But check out my video, the icon should be showing which you can click on. If I press =SUMIFS, open bracket, I can pick all this data - the Sum Range - and I need to anchor the columns...

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!

I need a Criteria Range. Because we're summarising by years the Criteria Range will be the years. I need to double anchor. I'm anchoring the columns and the rows, so I press F4 once. I'll press comma again and then I'll pick this 2015 and I'll anchor only the rows - the row 9. So I press F4 twice, close the bracket, press ENTER. Now, I press Ctrl here. Shift - I can actually do Shift + Ctrl + Down to the bottom and Shift + Up one, then to the right, press Ctrl + V to copy and paste, and you can see what it's done here it's summed all these cells where the criteria here is 2016.

I'll do something very similar with these seasons. I'll enter SUMIFS, open the bracket, select the Sum Range anchor the columns. I'll then pick a different Criteria Range - these seasons - double anchor, comma, and then pick the season here, anchor just the row, close the bracket, press ENTER, Ctrl + C to copy, Shift + Right and then holding Shift I go down, then press Ctrl + V to paste. And we can see again the sum of that 236,070. It's the same as this and then that is the same as that. So check out my video on SUMIFS!

In the second example we'll look at using the SUM function in what some people call a "punch-through" which is basically where we sum the same cell across multiple worksheets. We'll start with the same structure of data as we used in the previous example - the quantity sold of different items of clothing - but here we'll say that we've got 5 shops in London from Mayfair to Notting Hill. If we look at each of these worksheets it's the same structure of data but different quantities sold in each. Now, in this Consol worksheet I want to sum all of these 5 shops to give me a consolidated view of the data or of the company. I'll do that by summing in these cells.

Now, I can use SUM in the normal way I'll type =SUM( and then I'll select Mayfair, comma, then Chelsea, Kensington, Knightsbridge, and Notting Hill. We can see all of those line up in the formula there. Close the bracket, press ENTER - we get 296. Now, if I hold Shift + Ctrl + Down...Shift + Right, and I press Ctr + V...we've basically summarised our data. That's actually the 236,070 we saw in the previous example. I've got my SUMIFS over here which I showed you a demo of and we got the same total. Now, that has summed it.

That's the slightly long way. That's OK if you've got 5 worksheets, but let's say for instance in a workbook you have 50 worksheets. You don't really want to be clicking through each one because it takes a long time and you can make errors. There's an alternative to this, it's called a "punch-through". It does come with some risk in that you need all your worksheets to be exactly aligned.

So there we've picked cell P14 in each worksheet. To do a "punch-through" - I'll give you an example - this is how you do it, you type =SUM( I pick the 1st worksheet where I want to start summing. I pick the cell - so that's P14. I hold down Shift and then I click the last worksheet - that's Notting Hill. You can see this formula - we've got Mayfair, then we've got the colon, then Notting Hill and then P14. Normally when we sum some cells we have 'Mayfair' then a cell reference here. But we don't here, it's just summing across from Mayfair to Notting Hill, but always the same cell. I'll close brackets there, and then Ctrl and just paste this across. We get 236,070.

I can just see the "punch-through" working here. So we can see this gave us the right result but as I said this does come with some risk. Now, we know that total is this. I'll just show you, if I was to go into one of these worksheets and add an extra row. I'll insert an extra row there. I'll return to Consol and look at my total. It's actually changed, it's now 234,811. What has changed is that we were summing P14 across all the worksheets. In Chelsea now, P14 is this blank cell because we added in a row. So that's Risk 1 - we have to keep all these worksheets in exactly the same structure. So I'll delete that row and if we go back and look at the total it's this, which we're expecting.

Now, Risk 2 is if I took Chelsea and I moved it after Notting Hill, I go back to Consol, and again this has changed. It's because we are still summing between Mayfair and Notting Hill. It's between these sheets. And we've actually moved Chelsea out of the range. So we've got to ensure that we don't move worksheets in or out of the range when they're not supposed to be. So I'll move that between here. If I go back to Consol, I can look again at this sum - it's the total we're expecting.

To get around this some people like to add in worksheets at the start of these ranges and they'll put something like 'Start>>' and then they might put '<<End' and move it to the end of the range. These are kind of like bookends. It just shows that this is a range of worksheets and you're not supposed to move things in or out.

Now, like I say, this "punch-through", it can be super handy and it can be really quick for summing across worksheets, but like I say there is risk if you add in rows or columns or kind of change the structure, then this formula doesn't dynamically change with that. It doesn't capture the same cell every time. It will always just be the same cell that you're selecting in this consolidation sheet. So for me if I can get away with just adding each of these five worksheets then that's often the safer way.

So that's just a few of the applications of the SUM function. So if you like that then check out some of the other videos in my library Videos with Yellow thumbnails are Foundation-level, Green are Intermediate, Blue are Advanced, and Grey are Modeller-level. And don't forget to Like and Subscribe. Thank you for watching!