How to total a column of sales

“What did we sell this week?” is one of the most common questions a spreadsheet answers at work. A store manager wants the weekly total, a bookkeeper wants the month’s expenses, and a warehouse lead wants the number of boxes shipped. All three are the same task: add every number in a column and put the answer in one cell. The formula for that is SUM.

The example

A small coffee shop records its sales for each weekday. The day names are in column A and the amounts, in dollars, are in column B. The owner wants the week’s total in B7.

Step by step

1. Click the answer cell. Click B7, the empty cell under the last amount. A formula always goes in the cell where you want to see the result, not in the cells that hold the numbers.
2. Type an equals sign. Every formula starts with =. It tells the spreadsheet “calculate this” instead of “store this text.”
3. Type the function name and an opening parenthesis. Type SUM(. Capital or lowercase letters both work.
4. Give it the range. Type B2:B6. The first cell is where the numbers start; the last cell is where they end. The colon means “and everything in between.” You can also drag across B2 to B6 with the mouse, and the spreadsheet will type the range for you.
5. Close the parenthesis and press Enter. The finished formula is =SUM(B2:B6). After you press Enter, B7 shows 1900.

Why use SUM instead of plus signs?

You could type =B2+B3+B4+B5+B6 and get the same 1900. That works for five numbers, but it gets long and easy to break with fifty rows. One missed cell gives a wrong total that looks believable. SUM takes the whole block at once, so the formula stays short no matter how many rows there are. It also skips text, so a stray label inside the range will not cause an error.

Common mistakes

• The range stops too early. If someone adds Saturday in row 7 and moves the total down to B8, a formula that still says B2:B6 will leave Saturday out. When rows are added at the end of a list, check that the range reaches the last row. Inserting a row inside the range usually stretches the range for you; adding one just below it does not.
• The total cell is inside its own range. If B7 contains =SUM(B2:B7), the formula tries to add itself. The spreadsheet will warn about a “circular reference” and may show 0. The range should end on the row above the total.
• A number is stored as text. Numbers copied from emails or websites sometimes arrive as text. They often line up on the left side of the cell instead of the right. SUM skips text, so the total comes out too small with no error message. Retype the number, or use the spreadsheet’s “convert to number” option.
• The equals sign is missing. Without =, the cell simply shows the words SUM(B2:B6). Click the cell, add the equals sign at the front, and press Enter.
• Totaling the wrong column. In a table with both “Units sold” and “Sales ($)” columns, it is easy to grab the wrong one. Read the column heading before you choose the range.

Questions first-time users ask

Does the total update if a number changes?

Yes. That is the main reason to use a formula instead of typing 1900 yourself. If Thursday’s sales are corrected from 360 to 380, B7 changes to 1920 straight away.

Can I add two separate columns?

Yes. Separate the ranges with a comma: =SUM(B2:B6, D2:D6) adds both blocks together. You can also mix ranges and single cells, such as =SUM(B2:B6, F1).

Is there a faster way?

Most spreadsheet programs have an AutoSum button (Σ). In Microsoft Excel, clicking the cell under a column and pressing Alt and = together writes the SUM formula for you. Always look at the suggested range before pressing Enter, because it guesses. For a quick look without writing a formula, select the numbers. Excel shows their sum in the bar at the bottom of the window, and Google Sheets shows it in the bottom-right corner.

Does SUM include hidden or filtered rows?

SUM adds every row in the range, including rows you have hidden or filtered out. If you need a total of only the visible rows after filtering, use =SUBTOTAL(9, B2:B6). The 9 tells SUBTOTAL to add.

Will this work in Excel and Google Sheets?

Yes. =SUM(B2:B6) is written the same way in Excel, Google Sheets, LibreOffice Calc, and Apple Numbers.

Practice it

Related guides: finding an average · preparing an invoice · spreadsheet basics · all guides