Using SUM and AVERAGE Functions

SUM and AVERAGE Functions

The SUM and AVERAGE functions are probably the two most commonly-used functions in Excel – because they’re amazingly simple to use, and because we tend to add up and average things a lot.

To make sure you know how to use them, and to use them for more than a simple single contiguous range of cells, let’s look at them now, using the Selected Cities sheet, which contains population data from the Census.

First, when I go to cell D80, I can do a quick AUTOSUM of the 2020 populations of the cities in California included in this particular list.

To do this, I click in the cell and then click the AUTOSUM button on the Home tab. Instantly, I have a sum of the cells above the cell containing the function, and as Excel is very good at guessing which adjacent cells one wants to SUM. The range is correct, and so when I press Enter to accept the SUM, it reflects the total of cells D5 through D79.

I can then use the Fill handle to repeat that formula for the 2010 Census data, dragging the AutoSUM into column E, to cell E80, and the SUM now includes cells E5 through E79. This is an example of relative addressing, which we cover in another video in greater detail, but suffice to say, when you use the Fill Handle to copy a formula from one column to the next, the cell addresses update to reflect the current location of the formula.

Now let’s get the AVERAGE of those same ranges, by clicking in cell D81, and clicking the drop arrow on the AutoSUM button, and choosing AVERAGE from the list. Of course, I can also type =AVERAGE and then press the TAB key, but with the AutoAVERAGE command, Excel guesses which cells I want to Average, and that’s a big help.

In this case, however, Excel included the AutoSUM result in cell D80, so I need to edit the range – and I can do that easily by pressing the SHIFT key and clicking in cell D79, to tell Excel, “No, stop there and end the range on the cell I’m clicking in.”

Alternatively, I could have dragged through the desired range and omitted that last cell, or I could have edited the range in the function directly, by typing and changing D80 to D79.

In any case, as soon as I press Enter, I’ve got the Average population for the cities in California for 2020, and I can use the Fill Handle to repeat the function in cell E81.

Now, let’s quickly calculate the totals and averages for each of the remaining states – you can watch as I use AutoSUM and AutoAVERAGE at the end of each range of cities, editing ranges as needed before completing the functions.

And then, to demonstrate how to customize the ranges to be summed or averaged, I’m going to insert totals for all 4 states and averages, too.

Starting in cell D160, I’ll click the AutoSUM button. Excel is confused by the location of adjacent numbers, so I’m going to need to redirect it. To do so, I’ll click in the Texas total, and then scroll a bit, press the CTRL key and click in the New York total, scroll again and CTRL + click in Florida’s total, and then finally, scroll and CTRL+click on California’s total. Now my SUM range is the series of 4 totals, one per state, and I can press Enter to tell Excel to complete the calculation.

And I can again use the Fill Handle to make that repeat – in the tandem cells – for the 2010 Census numbers.

Of course, we can do an AutoAVERAGE for the 4 states, but given that we don’t need Excel to guess which cells I want to Average – it would have no way of doing so accurately – I’ll click in cell D161 and type =AVERAGE, press TAB, and then drag through all of Texas’ 2020 numbers.

Then I’ll scroll up to the New York numbers, press the CTRL key and drag through cells D112 through D108,

scroll up to Florida’s numbers, press CTRL and drag through cells D104 through D83,

scroll up to California’s numbers, and with CTRL pressed again, drag through cells D79 through D5.

Now I can close the parentheses, and press ENTER.

And again, I can use the Fill Handle to repeat that for the 2010 numbers.

Pretty powerful, very easy, and very simple to modify in terms of which numbers you need summed or averaged.

No wonder SUM and AVERAGE functions, whether built manually or triggered by their Auto versions on the Home tab, are so popular!