Quickly Sum a Column in Excel

[nerdslife_team]

It’s a common task to need to total up numbers in Excel, whether it’s for sales data, price lists, or any other kind of information organized in columns or rows. You might be wondering what the best ways are to quickly get these sums. There are actually quite a few options, and we’re going to explore them here, including one-click methods, the AutoSum feature, the SUM function, filtering, the SUMIF function, and even using tables.

Method 1: Using the Status Bar for a Quick Sum

The absolute quickest method to get a total of a column is to select the whole column and then check the status bar at the bottom of your Excel window. The status bar automatically displays the sum (among other things) for any selection you make.

Let’s say we have a list of prices in column B that we want to add up.

To do this, click on the letter “B” at the top of the column to select the entire column. Then, look down at the bottom of the Excel window. You should see the sum of your column, right there in the status bar.

You’ll see that the sum is shown there, along with the average and count of the selected cells.

You don’t need to select the entire column, you can just select a specific range of cells like B2 to B11 and you’ll still see the sum in the status bar. This trick also works for totaling a row by selecting a row of values.

The best part? This method is smart. If your selection includes text, it’ll ignore that and only add up the numbers. For example, even if you select the whole column, including the header, it will correctly sum only the numerical values.

---

Method 2: Using the AutoSum Function

Another efficient way to sum a column or row is the AutoSum feature. It’s a handy tool that uses the SUM function to quickly add up values in a column or row.

You’ll find the AutoSum button in both the ‘Home’ tab and the ‘Formula’ tab of the Excel ribbon. This button will insert the SUM function in the cell you have selected.

Imagine you have the same table of prices, and you’re going to add the sum in the first empty cell just below the column B of prices.

Go to the ‘Formula’ tab and click the ‘AutoSum’ button in the ‘Function Library’ group.

Alternatively, you can go to the ‘Home’ tab and click the ‘AutoSum’ button in the ‘Editing’ group.

Either way, this will insert =SUM() in the selected cell and highlight the range with your numbers, like ‘marching ants’ around the range. You can check to see if the range is correct. If it isn’t, simply drag the selection to the correct range to change the function’s parameters.

Then, press Enter to see the total.

You can also use a shortcut to do the same thing. Select the cell below your column of numbers and use the following shortcut:

Alt+= (Press and hold the Alt key and press the equal sign = key)

This will also insert the SUM function and highlight the range. Press Enter to finalize the sum.

AutoSum is really convenient for quick sums. However, it might not correctly identify the range if your column has empty cells or text values.

For instance, if there’s an empty cell in the middle of your numbers, AutoSum might not include all of the correct cells. If cell B6 was empty, AutoSum might only select up to B5.

To fix this, you’d have to correct the selected range by dragging with your mouse or typing in the correct cell references, and then press Enter.

To avoid these potential range selection issues, you can always manually enter the SUM function.

---

Method 3: Manually Using the SUM Function

While AutoSum is fast, sometimes you’ll want to use the SUM function manually. This is particularly useful if you only want to sum a portion of your column, or if your column includes blank cells or text values that cause issues with AutoSum.

The SUM function also gives you the flexibility to display the sum anywhere on your worksheet, not just directly below the column. You can calculate the sum or total of cells anywhere in the worksheet.

The syntax of the SUM function is:

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

• number1 (required) is the first number or cell reference to include in the sum.
• number2 (optional) is any additional number or cell reference to include in the sum.

You can add up to 255 arguments. You can specify specific cells, ranges of cells, or a combination.

Here’s how to use the SUM function manually:

1. Select the cell where you want to show the total. Start typing =SUM( in the cell.

2. Select the range of cells you want to sum. You can drag with your mouse, or use the shift key and arrow keys to select the range. You can also enter cell references manually: type the first cell’s reference, then a colon, then the last cell’s reference.

3. Close the bracket and press Enter to see the result.

The function will give you the sum of the cells even if the column has text values or empty cells, as long as the selection you make consists of valid numeric cells.

Summing Non-Contiguous Cells in a Column

You can also sum non-continuous cells using the SUM function. Hold down Ctrl and click on each cell you want to include, or enter the cell references manually, separated by commas in the formula.

This will add up only the values in the cells you selected.

Summing Multiple Columns

To sum multiple columns, select the columns you want with the mouse, or type the cell references of the first cell in the range, followed by a colon, followed by the cell reference of the last cell in the range.

Close the bracket, and press Enter to see the sum.

Summing Non-Adjacent Columns

To sum non-adjacent columns, select any cell where you want the sum to appear, type =SUM(, then select the first range with your mouse or type the range manually. Add a comma, then select the next range or type its reference, and so on.

Add as many ranges as you want this way, separating each with a comma.

Close the bracket, then press Enter to see the final result.

Summing a Column Using a Named Range

If you have a really big worksheet, using named ranges in the SUM function can make things much easier and quicker to calculate the sum of the columns. Named ranges allow you to refer to sets of cells using a simple, easy-to-remember name.

Another advantage of named ranges is you can refer to a dataset in another worksheet and sum the values in the current worksheet.

To use a named range, you have to create one first. Here’s how:

1. Select the range of cells you want to name (excluding headers). Go to the ‘Formulas’ tab and click ‘Define Name’ in the ‘Defined Names’ group.

2. In the ‘New Name’ dialog box, specify the name for your range. You can also set the scope of the name, either the whole workbook, or just a specific worksheet. Click ‘OK’.

You can also change the range if it’s incorrect in the ‘Refers to’ box.

Alternatively, you can use the ‘Name’ box, which is to the left of the formula bar. Select the range, type the name in the Name Box and press Enter.

However, the Name Box always sets the scope to the entire workbook.

Now, you can use the named range in a formula.

Select an empty cell, and type the SUM function using the name of your range as the argument. For example:

=SUM(Prices)

then press Enter.

The above formula in Sheet 4 refers to the named range ‘Prices’ in Sheet 2 and returns the sum of all values in the ‘Prices’ column.

---

Method 4: Using SUBTOTAL to Sum Only Visible Cells

If your dataset has hidden or filtered rows, using the regular SUM function might not give you the results you’re looking for, because it includes filtered or hidden cells in its calculation.

Let’s see what happens when you sum a column with filtered rows using the SUM function.

In the example above, we have filtered column B by prices less than 100. If you look at the row numbers, some are missing, so that means we have hidden/filtered rows.

If we try to get the sum of only the visible cells in column B, we expect to get 207 as the sum value. But the sum function returns 964.

This is because SUM adds all cells, visible or not.

If you only want to sum the visible cells when a column has filters or hidden rows, you need to use the SUBTOTAL function.

Using the SUBTOTAL Function

SUBTOTAL is a built-in function that lets you perform different operations (SUM, AVERAGE, COUNT, MIN, etc.) on data ranges, but it only summarizes data in the visible cells, ignoring any filtered or hidden rows. This gives the total only for the visible cells.

The syntax of the SUBTOTAL function is:

=SUBTOTAL(function_num, ref1, [ref2], ...)

• function_num (required) specifies what operation to perform. It can be a number from 1 to 11 or 101 to 111. We need to use ‘9’ for summing visible cells.
• ref1 (required) is the range of cells to which we want to apply the operation.
• ref2 (optional) Additional ranges can be added, and are optional.

---

Summing a Column with SUBTOTAL

To sum visible cells and exclude hidden/filtered ones, follow these steps:

1. Filter your table. Click on any cell within your data and navigate to the ‘Data’ tab and click the ‘Filter’ icon.

2. Arrows will appear next to the column headers. Click the arrow next to the column you want to filter, and select a filter option. Here we’ll filter for prices less than 100.

3. In the Custom AutoFilter dialog box, we’re setting the filter criteria.

4. Now, the numbers in the column are filtered.

5. Select the cell where you want the sum to appear, type the SUBTOTAL function. Once you open the function and add the bracket, a list will appear. Select ‘9 - SUM’ from the list, or type ‘9’ manually as the first argument.

6. Select the range of cells to sum, or type the reference. Close the bracket and press Enter.

Now you will get the sum of only the visible cells, which is 207.

Alternatively, you can select the range of numbers and click AutoSum under either the ‘Home’ tab or ‘Formulas’ tab.

This will automatically add the SUBTOTAL function at the end of the table and calculate the correct sum.

---

Method 5: Convert Your Data Into an Excel Table

Another way to quickly sum a column is by converting your data into an Excel Table. When you convert your data into a table, it will enable you to perform many different operations.

To convert your data into an Excel table:

1. Select any cell within your data. Then, go to the ‘Insert’ tab and click ‘Table’.

Or, you can use the shortcut Ctrl + T.

2. In the ‘Create Table’ dialog box, confirm the range and click ‘OK’. If your table has headers, make sure the ‘My table has headers’ option is checked.

This will convert your dataset into an Excel table.

3. Select any cell in the table. Navigate to the ‘Design’ tab, which will only appear when you select a cell within the table. Then, check the ‘Total Row’ option in the ‘Table Style Options’ group.

A new row will immediately appear at the end of your table with totals.

Click on any cell in that new row, and you’ll see a drop-down arrow. Select the drop-down in the cell at the bottom of the column you want to total, and make sure SUM is selected.

You can also change this to other functions, like ‘Average’, ‘Count’, ‘Min’, etc.

---

Method 6: Summing Based on Criteria Using SUMIF

All of the previous methods sum an entire column. If you only need to sum specific cells based on a condition, you’ll need to use the SUMIF function.

The SUMIF function sums cells that meet a specified condition.

The syntax of the SUMIF function is:

=SUMIF(range, criteria, [sum_range])

• range: The range of cells where you look for the criteria.
• criteria: The condition you’re checking. It can be a number, text, date, cell reference, etc.
• sum_range (optional): The range of cells containing the values you want to sum. If this is not provided, the range from the first argument is summed instead.

Let’s say you have sales data for different regions and you only need to sum the sales for the ‘South’ region.

You can use this formula:

=SUMIF(B2:B19,"South",C2:C19)

Select the cell where you want the sum to appear and type in the formula. This formula will look for the word “South” in column B2:B19 and add the corresponding Sales amounts from column C2:C19, displaying the result in cell E7.

Instead of hard-coding text into the formula, you can also reference a cell that has the text condition.

=SUMIF(B2:B19,E6,C2:C19)