How to Total a Column in Excel

It is possible to sum up columns with just one clickusing by using the AutoSum feature, the SUM functions, filters feature, and also by converting a data set into tables.

Making rows or columns of figures is something that most of us have to frequently. For instance, if you keep important data like price lists or sales records within the cells of one column, you might need to know quickly the sum of the column. Therefore, it is important to understand how to add up columns in Excel.

There are a variety of ways that to sum or add up rows or columns in Excel such as, with one click or using the AutoSum function, SUM function, filter feature, SUMIF function, and using the conversion feature to convert a data set into tables. In this article, we’ll examine the various methods of creating rows or columns in Excel.

SUM A Column in one click using the Status Bar

The simplest and fastest method to determine the value of the column is by clicking on the column’s letter with the numbers , and then check the bar labeled ‘Status’ on the right. Excel includes a Status bar that is located at the lower right in the Excel window. It displays different information about the Excel worksheet, including the count, average and the sum of the cells selected.

Let’s say you have a table with information as follows and you are looking for the sum of prices within column B.

All you need to do is choose the entire column that contains the numbers you wish to add (Column B) by clicking on that letter at the bottom of your column. Then take a look at Excel’s Excel status bar (next next to zoom controls).

You will be able to be able to see the total value of the selected cells , along together with average as well as count values.

You may also choose the B2-B11 data range instead of the entire column, and then check the Status bar to find the total. You can also see the total number of numbers within one row by choosing the row of numbers instead of a column.

The advantage of the method described above is that it automatically does away with cells that have textual values and simply adds up the numbers. You can observe above that when we select the entire column B, including cell B1 that has the text name (Price) It only added up the numbers in the column.

SUM a Column using the AutoSum Function

Another way to quickly sum the columns in Excel is to use Excel’s AutoSum feature. AutoSum is a Microsoft Excel feature that allows you to quickly add up a range of cells (column or row) containing numbers/integers/decimals using the SUM function.

There is an AutoSum command button that is located on both the ‘Home’ as well as the ‘Formula’ tabs on the Excel ribbon. It will add the function ‘SUM’ into the cell you have selected when it is pressed.

If you have the table of data illustrated below. You want to add up the data in column B. Choose an empty cell below the column or at the left-hand side of a row of data (to add the row) you have to add.

Select the tab ‘Formula’ and then click the button ‘AutoSum’ in the Function Library group.

Go to the tab ‘Home’ and then click the button ‘AutoSum’ within the Editing group.

In either case, when you press the button, Excel will insert the word ‘=SUM()” in the cell you have selected and highlight the range using your numbers (marching insects all over this range). Verify that the range you select is correct. If it’s not the right range you can alter it by choosing another range. The parameters of the function will automatically adjust in accordance with that.

After that, click Enter at the keyboard and you will display the total of the column within the cell you have selected.

You can also activate the AutoSum function by using the keyboard shortcut.

To do this, click on the cell that is just below the last cell of the column for which you would like to have the total. You can then use the following shortcut:

Alt= (Press while holding the Alt key, then press an equal symbol = key

It will then automatically add the SUM function, and then select the range to use. Press Enter to add the column’s total.

AutoSum allows you to quickly sum rows or columns by a single click or pressing the keyboard shortcut.

But, there is an inherent limitation with the AutoSum function. It will not be able to identify and select the right range in the event that there are empty cells within the range, or in any cell with an undefined text value.

In the previous example Cell B6 is not filled with data. When we enter into the AutoSum function in cell B12, it selects only five cells higher than. This is because the function believes that cell B7 represents the final cell in the data, and only returns 5 cells in total.

To fix this, you need to change the range by clicking-and-dragging with the mouse or type the correct cell references manually to highlight the whole column and press Enter. You will receive the correct result.

To prevent this from happening avoid this, you can use to the SUM feature manually in order to determine the sum.

SUM a Column by Entering the SUM Function Manually

While AutoSum is a great tool to use, the AutoSum command is fast and simple to use, there are times when you might need to use it manually. SUM method manually in order to determine the total of a column or row within Excel. Particularly, if you just need to sum up a few columns, or when your column has cells that are blank or have an arbitrary text value.

Additionally, if you wish to display your sum in any cell on the worksheet, apart from the cell below the column or following rows of numbers you may make use of SUM. SUM function. By using this SUM feature, you are able to determine the sum or total of all cells within the worksheet.

The Syntax of SUM Function:

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

• number1(required) is the first numeric value that needs to be added.
• Number 2 (optional) will be the 2nd additional number to be added.

While the first argument is the only argument that must be used You can add up to a maximum of more than 255 arguments. Arguments can be any of the numbers you wish to add together or references to cells that correspond to the numbers.

Another advantage to making use of SUM manually SUM feature manually is the fact that it allows you to add up the numbers of cells that are not adjacent of a row or column and also multiple rows or columns. This is how to utilize the SUM function by hand:

First, choose the cell you wish to view your total for a row or column anyplace in the workbook. Then, begin your formula by entering the formula =SUM( in the cell.

Select the cells that have the numbers you wish to add or type in the cell references for the range you wish to add in the formula.

You can click-and-drag using the mouse or press the shift key, and then utilize the arrow keys to select a specific range of cells. If you wish to manually enter cell reference enter the reference to the cell of the first cell in the range and then the colon, then the reference to the cell of the cell that is the last one in the range.

After entering the arguments then close the bracket and hit the Enter key to see the results.

As you can see, even if the column is empty cell as well as a Text value, this function will give you the total of the cells that were selected.

Summing Non-Continous Cells in a Column

Instead of summarizing the continuous cells in a row it is also possible to combine non-continuous cells into columns. To select cells that are not adjacent press the Ctrl key and then click on the cells you wish to add together or manually type in cell references and then separate them using comma (,) in the formula.

This will show the sum of the cells selected within the column.

Summing Multiple Columns

If you are looking for the sum of several columns, choose multiple columns using the mouse or type in the cell reference of the first column in the range and then an apostrophe, then the cell reference that is the final one of the range to be used as the arguments for the function.

After entering the arguments then close the bracket and hit the Enter key to view the results.

Summing Non-Adjacent Columns

You can also add non-adjacent columns by using SUM. SUM function. Here’s how:

Choose a cell on the worksheet that you wish to display the total of the columns that are not adjacent. Start the formula by typing in the formula =SUM( in that cell. Then, choose one of the column’s ranges using the mouse or type in the reference manually.

After that, you can add an apostrophe and choose the next range, or enter the second range reference. You can include any number of ranges you would like in this manner and then separate them by a comma. (,).

After the arguments are finished, close the bracket and hit Enter to see the results.

Summing Column using Named Range

If you have a huge sheet of data and need to quickly determine the total of the numbers in a column using named ranges within the SUM function to calculate the total. If you make Named Ranges, you can make use of these names instead of cell references, making it simple to reference the data set in Excel. It is simple to use a named ranges in Excel instead of scrolling through hundreds of rows to choose the range.

Another benefit of the use of Named ranges is the fact that it allows you to use the name of the data set (range) in a different worksheet using your SUM argument and then get the sum of the values on the worksheet you are currently working on.

To make use of a named range within formulas, first you must create one. This is how to create and utilize the named range within the SUM function.

The first step is to select the cell range (without headers) to which you wish to create the Named Range. Next, go to the tab ‘Formulas’ and click on”Define Name” option within the Defined Names group.

Within the New Name dialogue box, enter the name you wish to assign to the chosen range in the field ‘Name:. In the ‘Scope: field, you are able to alter your scope for the range you want to name to the entire Workbook or just a specific worksheet. The scope defines the scope of the named range. It can be set to be accessible to the entire workbook or just a particular sheet. After that, press the ‘OK’ button.

You can also alter the reference for the range by using the field ‘Refers to’ field.

Alternately, you can designate a range using the “Name” box. For this to do, click the range, then go to the box ‘Name’ to the left side of the bar for formulas (just below the letters A) and type in the name you want to assign to the chosen date range. After that you can press Enter.

However, when you create a named range by using the name box will automatically set the boundaries for the range named to the entire workbook.

You can now use the named range you made to quickly determine the sum value.

For this to be done, choose any empty cell in the workbook that you would like to show your Sum result. Then, type in the SUM formula using the name of the range and its arguments, and then press Enter:

=SUM(Prices)

In the example above the formula on Sheet 4 is referring to the column “Prices” in Sheet 2 to calculate the total of a column.

Sum Only the Visible Cells in a Column Using SUBTOTAL Function

If you have cells that are filtered or hidden cells within the data set or column, applying the SUM function to sum up the column is not recommended. Since the SUM function incorporates hidden or filtered cells in its calculation.

The following example illustrates what happens when you add columns with hidden or removed rows:

In the table above we have filtering column B with prices that are lower than. This means that we have filtered rows. It is evident that there are hidden or filtered rows in the table due to the rows that are not numbered.

When you add the visible cells in column B with the SUM function you should get the sum ‘207’ value , but instead it displays the number ‘964’. This is due to the fact that SUM functions also take the filtering of cells when formulating the sum.

This is the reason you can’t make use of the SUM function when filtering or hidden cells are involved.

If you don’t want hidden or filtered cells to be part of the calculation for adding a column, and you only want to add only the cells that are visible, then you must make use of SUBTOTAL. SUBTOTAL function.

SUBTOTAL Function

The SUBTOTAL is an extremely useful built-in function of Excel which allows you to run different calculations (SUM, COUNT, AVERAGE VARIANCE, COUNT, and more) for a variety of data. It will return either a total or aggregate of the column. The function can only summarize information in visible cells, ignoring filters and hidden rows. SUBTOTAL is a flexible function that performs 11 different functions within the column’s visible cells.

the Syntax SUBTOTAL functionality:

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

Arguments:

• function_num (required) – It’s an ID number for a function that indicates the function to be used for calculation of the total. This argument can be any number from 1 to 11 or 101-111. In this case, we must add the visible cells, without ignoring the cells that are filtered out. To do this, we have to use the number ‘9’.
• ref1 (required) (required) The first named range or reference you wish to be subtotal.
• ref2 (optional) ref2 (optional) A second reference or named range that you wish to add to your subtotal. After the initial reference, you are able to increase the number of references to 254. references.

Summing a Column with the SUBTOTAL Function

If you are looking to add visible cells and exclude hidden or filtered cells, then follow these steps to utilize SUBTOTAL. SUBTOTAL function to sum up a column:

The first step is to sort your table. To do this simply select any cell in the data set. After that, go to the tab ‘Data’ and then click the Filter icon (funnel icon).

Arrows will be displayed in the column headers. Select the arrow that is next to the column header for which you wish to sort the table. Select the filter option you want use to filter your information. In the example below, we will filter column B for numbers that are less than 100.

Within the Custom AutoFilter dialog box, we’re typing ‘100’ before pressing OK.

The columns are filtered using values lower than 100.

Select the cell you want to display the sum and begin typing the function SUBTOTAL. When you launch the SUBTOTAL function and enter the bracket, you will be presented with a list of functions that you could use to calculate the formula. Select ‘9 – SUM on the list, or enter ‘9’ manually in the form of the initial argument.

Select the number of cells you want to add or type in the reference manually, and then close the bracket. After that you can press Enter.

You will be able to calculate the total (subtotal) of the cells that are visible – – ‘207’

Alternately, you can choose your range (B2:B11) by entering the numbers you want to add together and then select ‘AutoSum’ from the Home or Formulas tab.

It automatically applies it’s SUBTOTAL feature at the bottom of the table, and then sums up the results.

Convert your data into the Excel Table to Get the Sum of Column

Another method you can utilize to add your column is to convert your spreadsheet’s information to the form of an Excel table. When you convert your spreadsheet data into tables, you will be able to not just sum your column, but also perform a variety of other operations or functions with your spreadsheet.

If your data isn’t already formatted in a table then you must transform it into the format of an Excel table. Here’s how to transform your data into the format of an Excel table:

First, choose any cell in the data set that you wish to convert into the format of an Excel Table. Next, open the tab ‘Insert’ and then click on the ‘Table’ icon.

You can also press the shortcut Ctrl+T, which converts the cells’ range to the format of an Excel Table.

Within the Create Table dialog box, select the range, then select OK. If your table is header-free make sure that “My table has headers” option unchecked.

This will transform your data set to the format of an Excel Table.

When the table is set you can select any cell in the table. Next, go to the tab ‘Design’ which is only available after you have selected a cell within the table. Check the box labeled “Total Row” under the Table Style Options’ section.

After you’ve checked the option ‘Total Row A new row will instantly appear at the bottom of your table, with the numbers at the bottom every column (as as shown below).

When you click one of the cells in the new row, you will be able to see a drop-down beside the cell, from which you can select an operation to calculate the total. Choose the cell in the end of the row (new row) of the column you wish to add, then click the drop-down beside it, and ensure that the “SUM” function is selected from the drop-down list.

You can also alter your function’s settings to average or Count, Min and Max to view their respective values on this new row.

Sum a Column Based on a Criteria

The previous methods taught how to calculate the total column. However, what happens if you wish to add only certain cells that satisfy certain criteria, rather than all the cells. You must make use of SUMIF instead. SUMIF method instead of using the SUM function.

The SUMIF function searches for an exact condition within the cell (column) before it adds all the values that satisfy the specified condition (or values that correspond to cells that satisfy the criteria). It can do this using number condition or date condition, text condition, wildcards as using empty and non-empty cells.

Syntax of SUMIF Function:

=SUMIF(range, criteria, [sum_range])

Arguments/Parameters:

• area range The cell range in which we search for cells that satisfy the criteria.
• Criteria Criteria used to determine which cells must be combined. The criteria could be a number, text string or date, expression, cell reference, wildcard character, logical operator and many other purposes.
• range (optional)– It’s the range of data that has values that are to be summed when the range entry is in line with the specified condition. If this argument isn’t provided, then the range is instead summed.

If you have the following data set, which contains sales information for each Rep from various regions. Suppose you want to only sum the amount of sales from the region of ‘South.

It is easy to do this by using the following formula:

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

Choose the cell in which you would like to display the result , and enter the formula. This SUMIF formula searches for the value “South” in column B2:B19, and then adds the appropriate sales amount for column C2:C19. Then , it displays the result in cell E7.

You may also use the cell to refer to one which contains text conditions instead of directly referencing the text within the argument for the criteria:

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

That’s it.