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.

13 comments
sparent
sparent

Given your scenario, wouldn't you want cell A17 to contain "Smith/Michaels" rather than "Smith/Jones"?

rwbyshe
rwbyshe

This document allowed me to copy and paste it into Microsoft Word... not sure if this is a new policy with TR but in the past if you tried Copy & Past it would have black text on a very dark blue background. This time it came out as it should! Don't know if TR screwed up or not but being able to save it was a nice change/opportunity!!!

rwbyshe
rwbyshe

Another really great tip from TR BUT.... Why is TR so damn reluctant the last couple of years to allow you to save the posts as a file on your computer? Wouldn't that be soooo very nice to keep it in the folder with the sample Excel files. Sorry TechRepublic but you stopped making sense with these tips when you quit allowing your members to download these onto our computers!!!

Dan@DanLind.net
Dan@DanLind.net

Great tip, Susan. Maybe I'm missing something, but in the example where you add Smith's sales and Jones' sales, the total looks to be wrong by one cent. What happened?

jbenton
jbenton

Pressing alt-= will also invoke AutoSum Click AutoSum a second time instead of enter to accept the default range The down arrow on the right of the AutoSum button accesses different commonly-used functions (btw, the AutoSum button looks like a captial greek sigma) Select a range terminated by a blank cell, click AutoSum and Excel will sum the range into the blank cell; select a range without a blank cell and the results are not always predictable (in my experience, it offers a =SUM() in the first cell to overwrite your entry) Select all your data bounded bottom and right by blanks and Excel will sum rows, columns and give a grand total at the bottom right (usually by summing the column totals - always an impressive time-saver!)

rsdance
rsdance

I always get great value from every article Susan posts. I have learned a lot from you and really appreciate it. Happy Holidays Rupert

DonG43
DonG43

Great time saver. I have been using spreadsheets since 1982, but don't seem to learn the "new" stuff. Love to read your columns because I always learn something.

ssharkins
ssharkins

Can't help you, but I think TR does listen -- and I'm told, it's something they're working on. In the meantime, you can do a copy to a Word document -- a bit more work, but it beats a blank.

jody.burton
jody.burton

Cell A17 shows "Smith/Jones," while the data is for Smith/Michaels. But the reason for the seemingly erroneous sum is the values are only shown to 2 decimal points, while the actual values are not rounded. This is not visible in the article screenshots, but is in the download provided.

ssharkins
ssharkins

I always love your editorials -- good stuff to share.

ssharkins
ssharkins

I always love hearing that you guys are pleased and able to use my tips! I love hearing it! Thank you!

ssharkins
ssharkins

Numeric formats and rounding are almost always the reason for these discrepancies -- a huge topic in and of itself. I didn't notice it, or I would've added a short note about it. Thank you for bringing it to our attention, so we had the opportunity to explain it. Thanks jody.burton for stepping in!

Editor's Picks