The Excel SUM Function: A Beginner's Guide

The Excel SUM function adds up numbers: a column of sales, a row of monthly costs, or a few cells scattered across a sheet. You type =SUM(, the cells to add, and ). This guide is for beginners and job seekers who want to write SUM correctly the first time and spot a wrong total before anyone else does.

What does the SUM function do in Excel?#

SUM returns the total of the numbers you give it. A function is a ready-made calculation with a name. You call it by typing =, the name, and the inputs inside parentheses. Those inputs are called arguments.

The syntax is:

=SUM(number1, [number2], ...)

• number1 is required. It can be a number, a cell like B2 or a range like B2:B5.
• number2 and later are optional. You can add up to 255 arguments, separated by commas.

How to use the Excel SUM function, step by step#

The quickest way to learn SUM is to total a real column. Here is a small revenue table:

1. Click B6, the cell for the total.
2. Type =SUM(.
3. Select B2:B5 with the mouse, or type it.
4. Type ) and press Enter.

=SUM(B2:B5)

The result is 220,600. B2:B5 is a range: a block of cells written as the first cell, a colon, then the last cell. It means B2, B3, B4 and B5. The same formula in C6, =SUM(C2:C5), gives 137,800.

You don't have to type the formula at all. AutoSum and its Alt+= shortcut write it for you and guess the range.

Colon or comma: adding ranges and separate cells#

A colon means "from this cell to that cell". A comma means "and also this". Mixing the two lets one SUM handle almost any selection.

The third row is a common test question: "total the revenue without Q2". Q1 is a single cell and Q3 to Q4 sit next to each other, so =SUM(B2,B4:B5) does it in one formula.

A block like B2:C5 covers two columns and four rows, so SUM adds all eight cells. Use it when you want one grand total. If you want separate totals per column, write one SUM per column instead.

SUM vs the plus sign#

SUM is shorter and safer than adding cells one by one with +. =B2+B3+B4+B5 gives the same 220,600, but it has two weaknesses:

• It's easy to miss a cell. With 20 rows, a long chain of + is hard to check. A range is easy to read.
• It breaks on words. If a cell holds text such as "n/a", =B2+B3 returns the #VALUE! error. SUM simply skips that cell.

You can still mix the two when it reads better. =SUM(B2:B5)-SUM(C2:C5) gives the profit for the year: 82,800. If you are new to typing formulas, start with how to write your first Excel formula.

What does SUM ignore?#

Inside a range, SUM adds only real numbers. It skips:

• Empty cells. A gap in the column doesn't cause an error.
• Text, such as "absent" or "TBC".
• Numbers stored as text. These look like numbers but Excel treats them as words. They often sit on the left of the cell, sometimes with a small green triangle in the corner.
• TRUE and FALSE values in cells.

Skipping text is usually helpful, but it can hide a problem. If a value was imported as text, SUM leaves it out without any warning, and your total is too low. A quick check is =COUNT(B2:B5), which counts the real numbers. If it is lower than the number of rows, something is stored as text.

One thing SUM does not skip is an error. If any cell in the range shows #N/A or #DIV/0!, SUM shows that error too. Fix the error in the source cell first.

Common SUM mistakes (and quick fixes)#

Most wrong totals come from a handful of mistakes. Excel warns you when a formula breaks, but not when it gives the wrong answer.

1. The range misses a row. You added a new row under the data, but the formula still says B2:B5. Edit the range, or insert new rows inside it so it grows automatically.
2. Numbers stored as text. Retype the value, or convert the cells to numbers. SUM then includes them.
3. The total is inside its own range. =SUM(B2:B6) typed into B6 refers to itself. This is a circular reference: Excel shows a warning and usually returns 0. End the range one row above the total.
4. A subtotal is counted twice. If a range includes a subtotal row and the rows it adds up, those numbers are counted twice. Keep subtotals out of the range.

Each of these, plus hidden decimals and manual calculation, is explained in why your Excel total is wrong and how to fix it.

Where SUM fits in a job test#

SUM is the first function most employers check, often together with AVERAGE, MIN and MAX. Once you can total a column, the next step is to describe it: AVERAGE, MIN and MAX give the mean, the smallest and the largest values, and MEDIAN and MODE show the typical value when one number is far from the rest. For office and admin roles, totals like these are a core skill, as covered in Excel skills for admin and office jobs.

If you need a total with a condition, such as "only the North region", SUM isn't the right tool. Use SUMIF for one condition or SUMIFS for several. The SUM function reference lists the syntax and more examples in one place.

Practise it#

Reading about SUM is not the same as typing it under time pressure. Practise SUM and AutoSum in a live spreadsheet: you'll total revenue, add separate cells with commas and combine a range with a single cell. Then find and fix three broken totals in the Debugging SUM lesson, or try the exercise on this page now.