There are many situations in which you need to quickly sum the values in one or more columns or rows. Fortunately, this is very easy to do in Microsoft Excel using multiple methods. In this guide, you will learn six different ways to add columns in Microsoft Excel, depending on what you need. First, you will learn how to see the sum instantly by selecting the relevant cells. Second, you will learn how to use AutoSum to add the values in a column or row.
Third, you will learn how to use the SUM function to sum all the values in multiple ranges. Fourth, you will learn how to use the AGGREGATE function to sum only the visible cells in a range. Fifth, you will learn how to add a total row to an Excel table to automatically get the sum of the values in all columns or rows. Sixth, you will learn how to add the values in a column or row to the corresponding value in another column or row.
How to Sum a Column in Excel by Selecting It?
If you need to see the sum of a range of values quickly, you just need to select it and check the status bar. These can be in a column, row, or a combination of both.
However, if you need to use or refer to the sum from a different cell, you should use one of the other methods.
How to Sum a Column in Excel with AutoSum?
Select the column or row with the values you want to sum.
Go to the ‘Home’ or ‘Formula’ tab, click AutoSum, and select ‘Sum’.
If you want to find the sum of values based on one or more conditions in Google Sheets, the SUMIF and SUMIFS functions come in handy. Here’s how to use them.READ MORE
How to Sum a Column in Excel with SUM?
You can also sum all the values in columns or rows using the SUM function.
Type the SUM function in an empty cell and select the cells with the values you want to sum.
If you also want to sum the values in other ranges, add a semicolon before selecting each range of cells.
Add the closing parenthesis and press ‘Enter’ to see the result.
How to Sum Visible Cells in a Column in Excel with AGGREGATE?
If you want to sum a filtered column, the SUM function is not an option, as it sums all cells in the range even if they aren't visible.
- 1. Type the AGGREGATE function into an empty cell and select the SUM option as the first parameter (9).
- 2. For the second parameter, select the option to ignore hidden rows (5).
- 3. For the third parameter, add the range you want to sum.
- 4. Add the closing parenthesis and press ‘Enter’ to see the result.
There are many ways to combine multiple columns into a single column in Excel. Here's how to do it without losing any dataREAD MORE
How to Use an Excel Table to Get Column Totals?
If your column data is in an Excel table, you can add a totals row that sums the values in each column.
- 1. If your data is in a range, select any cell in it and press Cmd/Ctrl + t to turn it into an Excel table.
- 2. Go to the ‘Table’ tab and check the ‘Total Row’ checkbox.
- 3. As you can see below, the total was added below the last column.
- 4. Grab the fill handle and drag it left up to the first column. Any rows inserted will be automatically added to the corresponding totals.
- 5. To insert row totals, simply select the data for the first row, and on the ‘Formulas’ tab, click ‘AutoSum’.
- 6. As you can see, the monthly totals are now in the last column.
How to Sum Multiple Columns in Excel by Row?
To sum the values in one column to the corresponding values in one or more columns, select each column and use the plus sign (+) between them.
- 1. Type the equal sign and select the first column with values.
- 2. Add a plus sign (+) and select the second column with values. Repeat this step for any additional columns.
- 3. Press ‘Enter’ to apply. Excel automatically recognizes that it’s an array formula, so you don’t need the array formula shortcut.
There are many ways to sum columns or rows in Microsoft Excel. You know how to see the sum instantly in the status bar by selecting the relevant cells. However, this method only allows you to see the sum. If you need to reuse the sum, you need to calculate it in a cell. Fortunately, you know another five methods you can use.
You have step-by-step instructions on how to sum a column using AutoSum and using the SUM function, as well as how to sum only visible cells using AGGREGATE. You also know how to add a total row to an Excel table to get the sum of the values in each column. Finally, you know how to add the values in one column to the corresponding values in another using the plus sign (+).
To learn more about other Excel formulas and how to add different types of values in Google Sheets, check out these guides on: