For this lesson you need to open the sample file: First Quarter Sales and Profit 4, from your Sample Files folder.
In Lesson 2-7 you learned how to select a Non-Contiguous Range. You selected the range B4 to B8 and then held down the Control key and selected cells D4 to D8. You were then able to see that the sales for all branches in January and March combined were 257,000 by looking at the number on the bottom Status Bar.
But what about if you want to put that value into your worksheet? Well, now that you've got the hang of selecting non-contiguous ranges, you can use this skill in conjunction with your AutoSum skills, to create a formula that will calculate the total of a non-contiguous range.
So let's begin by typing the text: Jan/Mar Sales, into cell A10. I'll then press the Tab key to move to cell B10.
And notice that Column A isn't quite wide enough to contain the text: Jan/Mar Sales. So to correct that I hover the mouse cursor over the intersection of the Column A and Column B column header, until I see the re-size mouse cursor shape. I then click and drag to make the column slightly wider.
And now I'm going to enter an AutoSum function into cell B10. To do that I click the Home tab on the Ribbon and, in the Editing group, AutoSum.
Now you can see at the moment that AutoSum would simply return the value in cell B9. That's the cell above the AutoSum. But I want to return the value of the Total Sales for all branches in January and March.
So I'll begin by selecting the January sales figures. I position the mouse cursor in the centre of cell B4 and then click and drag down to cell B8. Now I hold down the Control key on the keyboard and I select cells D4 to D8.
And let's look at the formula in the AutoSum's function. It's B4:B8, D4:D8. And that's the correct notation for a non-contiguous range within an Excel function.
Notice how the range references are colour coded as well. B4 to B8 is colour coded blue and the cells are shaded blue. And D4 to D8 is shaded red.
To finish the AutoSum I need to either click the AutoSum button again, or press the Enter key on the keyboard. I think this time I'll press the Enter key. And you can see that in January and March the total sales for all branches for both months were 257,000.
All that remains now is to save your work. And I'll save into the folder above my Sample Files folder, with the new name: First Quarter Sales and Profit-5.
I click the Save button and you've now completed Lesson 2-10: Use AutoSum to Sum a Non-Contiguous Range.