Click the cell under the numbers, click AutoSum on the Home tab (or press Alt+=) and press Enter: Excel writes =SUM(B2:B7) for you. To see a total without a formula, select the column and read its Sum in the status bar.
B8 = 3500 Every number from B2 through B7: the whole budget column.
A column total is the formula most people write first, and it usually takes one click: AutoSum looks at the numbers above the cell and writes the SUM for you. You can also type the formula, or select the numbers and read the total off the status bar without writing anything.
What goes wrong is quieter. A total can come out too low with no error at all, because one of the numbers isn’t a number to Excel: it was pasted or imported as text. Below you total a team’s costs all three ways, then find the amount the total left out.
This is Witsheet, Witsy’s in-browser spreadsheet. It works like Excel and checks each step as you go.
Click the empty cell under the numbers, then AutoSum, the Σ on the Home tab. Excel writes a =SUM() of the numbers above the cell and draws a box around them. Press Enter to accept it.
To total a row, click the empty cell to its right instead.
The shortcut is Alt+= in Excel for Windows and ⌘+Shift+T in Excel for Mac.
In a browser, ⌘+Shift+T reopens the last tab you closed, so on a Mac, click the Σ in the sheet above.
To total several columns at once, select the empty cells under all of them and click AutoSum: each
one gets a SUM of its own column.
AutoSum guesses which numbers you mean, and its guess stops at a blank cell or at text. In a column with a gap, or with a word or a number stored as text partway up, it takes only the numbers below it. Look at the box before you press Enter, and if it missed some, write the SUM yourself.
=SUM(B2:B7) adds every number from B2 through B7: the colon means “through”. Type =SUM(, drag over the cells and press Enter, as in the walkthrough, and Excel adds the
closing bracket. A comma adds another range or cell, as in =SUM(B2:B7, D2:D7).
SUM leaves out empty cells and text, so a heading inside the range does no harm. It also leaves out a number stored as text, the usual reason a total comes out too low, below. To show each row as a share of the total, divide it by the total: percentages practice shows how.
=SUM(B:B) adds every number in column B, however long the list grows: a row typed at
the bottom counts as soon as it’s in. The heading is text, so SUM leaves it out. Put the formula in
another column, because inside column B it would be adding up its own cell. Witsheet doesn’t take
whole-column references yet, so try this one in your own Excel.
A range like B2:B7 stays as it was written, and a row typed under row 7 isn’t in it.
That’s the usual reason a total stops keeping up with a list. Use the whole column, or make the list
a table, which grows to take in a row typed under it: Excel table practice shows its Total Row.
SUM adds everything in its range. To add only the rows that match something, one team or one
product, use SUMIF: =SUMIF(A2:A7, "Sales", B2:B7) adds the costs on the rows that say
Sales. SUMIF practice takes it further. A filter hides rows from view
but not from SUM, and sort and filter practice shows SUBTOTAL,
which adds only the rows a filter leaves showing.
Select some cells, and the status bar along the bottom of the window shows their Sum, with their Average and a Count of the cells that aren’t empty. It’s the quickest way to check a total, and it leaves nothing on the sheet. To total a whole column this way, click its letter.
Right-click the status bar to choose what it shows: the Minimum, the Maximum and a count of the numbers alone are there too. Like SUM, it leaves out numbers stored as text, so it agrees with a total that’s too low.
When a total is lower than it should be and there’s no error, one of the numbers usually isn’t a number to Excel. Amounts from an export, a paste from a web page or a cell formatted as Text can arrive as text that looks like a number. It sits on the left of its cell, where text goes, and SUM, COUNT and the status bar’s Sum leave it out without a word.
C8 = 2505 Comfortably under budget, and wrong. Software’s 1250 sits on the left of its cell: it’s text, and SUM leaves text out without a word. The costs come to 3755.
Excel marks each one with a small green triangle in the corner of its cell. To fix one, type the
number in again. To fix a column, select the cells, click the warning sign beside them and choose
Convert to Number, or write =VALUE(C3) in a spare column and copy it down. Witsheet has
neither the triangle nor VALUE yet.
A cell formatted as Text is the stubborn case. Whatever you type into it stays text, and changing its format back to General doesn’t convert what’s already there. Set the format to General first, then type the number in again.
The other usual causes are in the range, not the numbers: AutoSum stopped at a blank cell or at text partway up, or rows were added under a range that ends above them, both covered above. A total that’s too high usually counts rows you can’t see, because SUM adds hidden and filtered rows too.
Witsy’s Excel course teaches a few hundred small skills, called Wits, the way this page teaches column totals: in a real spreadsheet, a step at a time. The placement test finds the ones you already have.
Take a few minute test to see how good you actually are with spreadsheets. Witsy creates a map of the Wits you should learn next.
Take the placement test Or just start learning →