Mixing Ranges and Single Cells in SUM

So far you have given SUM one tidy range like A1:A10. But real spreadsheets are rarely that neat. Numbers you want to add are often scattered across the sheet.

The good news: SUM accepts multiple arguments. You can list several ranges and single cells in one formula, separated by commas.

In this lesson you will combine ranges and individual cells inside a single SUM call.

The full shape of SUM allows many arguments:

=SUM(arg1, arg2, arg3, ...)

Each argument can be a range or a single cell. SUM adds all of them together into one total.

The comma is the key. It separates one argument from the next, telling SUM to keep adding everything you list.

Suppose January sales sit in A2:A5 and February sales sit in C2:C5. To total both months at once:

=SUM(A2:A5, C2:C5)

SUM adds every number in the first range, then every number in the second, and returns the grand total.

The two ranges do not need to touch or be the same size. SUM handles them independently.

You can mix a range with a lone cell. Imagine daily sales in B2:B6 and a one-off bonus in cell B10:

=SUM(B2:B6, B10)

SUM totals the five daily figures and then adds the single bonus value.

This is perfect when one extra number lives apart from your main list but still belongs in the total.

Quarter sales sit in three separate blocks: Q1 in A2:A4 (100, 150, 200), Q2 in C2:C4 (120, 130, 140), and a special adjustment of 50 in cell E2.

The formula =SUM(A2:A4, C2:C4, E2) returns 890: 450 from Q1, plus 390 from Q2, plus the 50 adjustment.

One formula pulls together three different locations into a single clean total.

If your numbers are truly scattered, you can list individual cells one by one:

=SUM(A1, C3, F7, B12)

SUM adds just those four cells. This is like writing =A1+C3+F7+B12, but it reads more clearly and is easier to extend.

If one of those cells is blank or holds text, SUM simply treats it as nothing.

You are free to combine any mix of ranges and single cells in one formula:

=SUM(A1:A10, C5, D2:D8, F1)

SUM works through each argument in order and adds them all. There is no limit that matters for everyday work, so list as many pieces as you need.

This flexibility is what makes SUM the workhorse of spreadsheet math.

In most spreadsheets the argument separator is a comma. However, some regional settings (common in parts of Europe) use a semicolon instead:

=SUM(A2:A5; C2:C5)

If a comma gives an error, try a semicolon. The function is the same; only the punctuation between arguments changes based on your system's locale.

A common pattern is to total several subtotal cells. Say each department already has its own SUM in cells B5, D5, and F5. To get the company total:

=SUM(B5, D5, F5)

You are summing the subtotals rather than re-adding every raw number. This keeps a big sheet organized: small totals feed into a grand total.

One trap with multiple arguments: do not let your ranges overlap. If you write =SUM(A1:A10, A5:A15), the cells A5 through A10 are added twice, inflating the total.

Likewise, do not include a subtotal cell that already sums the same numbers you are listing.

Before pressing Enter, make sure each value is counted exactly once.

Multi-argument SUM works the same in Microsoft Excel and Google Sheets. You can list ranges and cells with commas in both.

The only difference you might notice is the comma-versus-semicolon separator, which depends on your regional settings, not the program.

Master this and you can total almost any layout of numbers with a single formula.

Test your understanding of mixing ranges and cells in SUM.

You learned to total scattered numbers in one formula:

• SUM accepts multiple arguments: =SUM(arg1, arg2, ...).
• Each argument can be a range or a single cell.
• Combine them freely, like =SUM(A2:A4, C2:C4, E2).
• Avoid overlapping ranges so nothing is counted twice.

Next, you will discover AutoSum, a shortcut that writes these totals for you.