Software

Use Excel's AutoSum to sum non-contiguous values

You know how to use AutoSum to add contiguous ranges, but you can also use it with non-contiguous ranges.

Summing is an easy task in Excel and perhaps the simplest way is to use AutoSum. You select a series of values and click AutoSum. It couldn't be easier. However, did you know it's almost as easy to use AutoSum to sum a set of non-contiguous values?

About AutoSum

Before we tackle using AutoSum with non-contiguous values, let's review AutoSum a bit. Using the sheet shown below, let's review a few ways to use AutoSum. First, if you wanted to sum Smith's monthly totals, you could do so in one of two ways:

  • Select B3:B14 and click AutoSum.
  • Select B15, click AutoSum, and press Enter.

That was simple, wasn't it?

The sheet is tracking monthly values for several people. You don't have to sum them individually. You could apply the same steps to a contiguous range, as follows:

  • Select B3:F15 (include the blank row at the bottom of the data set) and click AutoSum.
  • Select B15:F15, click AutoSum, and press Enter.

You can just as easily apply AutoSum to return monthly totals by selecting G3:G14 and clicking AutoSum.

Summing non-contiguous values

In the previous examples, all of the summed values were contiguous (adjacent). Sometimes the values you want to add won't be in nice neat contiguous blocks, but you can still use AutoSum. For instance, let's suppose you want to sum the monthly values for Smith and Michaels - not Smith, Jones, and Michaels - only Smith and Michaels.

You could select each column for Smith and Michaels separately and use AutoSum, then add those results, as follows:

  1. Select a blank cell, say B17, and click AutoSum. Excel will enter the function =SUM(B15).
  2. Enter a comma after B15, click D15, and press Enter.

That was fairly simple, but it worked only because you had the subtotals for each person. What if you don't have subtotals and furthermore, didn't want them? Delete the functions in row 15 and B17 and let's do it again, as follows:

  1. Select B17 and press AutoSum. Excel will enter a SUM() function for B3:B16.
  2. Although they do no harm right now, you don't want to include B15:B16. To remove those two cells from the reference, hold down the [Shift] key while you click B14. Doing so will change the reference from B3:B16 to B3:B14. (If it changes the reference to only B14, you didn't hold down the [Shift] key - try again.)
  3. With the first reference now B3:B14, hold down the [Ctrl] key and select D3:D14 to add that range as the function's second reference.
  4. Press Enter, and the function will return the yearly total for both Smith and Michaels (and no others).

Using AutoSum to sum contiguous ranges is a simple task. Accommodating non-contiguous ranges is also simple - the key is using the [Shift] and [Ctrl] keys to change and add references.

Two Excel files are available for demonstration purposes.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks