Download Article
Add values for an entire column or range
Download Article
This wikiHow will show you how to sum columns in Microsoft Excel for Windows or Mac. Use the AutoSum feature to quickly and easily find the total sum of a column’s values. You can also make your own formula using the SUM function! We’ll cover how to add the values of individual columns and entire cell ranges.
Things You Should Know
- Go to Formulas > AutoSum to automatically add up a column.
- Use the SUM function to add individual or multiple columns.
- To add multiple columns, select the cell range containing each column you want to sum.
Steps
-
Click the cell directly below the values you want to sum. For example, if you have values in cells A1 through A5, you would click A6. [1] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
- If you’re just getting started with Excel, check out our intro guides for making a spreadsheet and formatting a spreadsheet .
-
Click the Formulas tab. This will display various formula options in the top bar.Advertisement
-
Click AutoSum . This will open a drop down menu with AutoSum options.
-
Click Sum . This will automatically create a SUM function that adds the values of the column. You’ll see the values that are going to be summed in a highlighted selection box.
-
Press ↵ Enter or ⏎ Return on your keyboard. This will confirm the SUM formula and show you the sum total in the cell you selected.
Advertisement
-
Click a cell below the column you want to add up. Doing so will place your cursor in the cell. This method uses the SUM function , which adds all of the values in a given cell range.
- If you’re looking for subtraction information, check out our guide to subtracting in Excel .
-
Enter the "SUM" function. Type =SUM() into the cell.
-
Enter the column's range. Type the top cell in the column, a colon, and the bottom cell in the column into the parentheses.
- For example, if you're adding values in the A column and you have data in cells A2 through A10 , you would type in the following: =SUM(A2:A10)
- If your dataset has a header row, don’t include the header in the SUM formula.
-
Press ↵ Enter or ⏎ Return . This will display the sum of the column in your selected cell.
-
Create the sums of the other columns you want to add. You can create SUM formulas for each column, or copy the first formula:
- To quickly sum other columns of the same length, you can press Ctrl
+ c
(Windows) to copy the cell with the SUM formula, then press Ctrl
+ v
(Windows) to paste it under the other columns.
- On Mac, these shortcuts are ⌘ Cmd + c and ⌘ Cmd + v respectively.
- Pasting the formula will automatically change the cell references to the column that corresponds with where you pasted the formula.
- To quickly sum other columns of the same length, you can press Ctrl
+ c
(Windows) to copy the cell with the SUM formula, then press Ctrl
+ v
(Windows) to paste it under the other columns.
Advertisement
-
Determine which of your columns is the longest. This method adds up multiple columns in one formula. In order to include all of the cells in the longest column, you'll need to know to which row the column extends.
- For example, if you have three columns and the longest one has values from row 1 through row 20, your formula will need to include rows 1 through 20 for each column you want to add even if this includes blank cells.
-
Determine your beginning and ending columns. If you're adding the A column and the B column, for example, your beginning column is the A column and your ending column is the B column.
-
Select a blank cell. Click the cell in which you want to display the sum of your columns.
-
Enter the "SUM" command. Type =SUM() into the cell.
-
Enter the cell range. For a range of cells, the left cell in the range is the top-left cell, and the right cell is the bottom-right cell. These two cells define the range.
- In other words, type the first column letter, the first row number, a colon, the last column letter, and the last row number.
- For example, if you're adding columns A, B, and C, and your longest column stretches to row 20, you would enter the following: =SUM(A1:C20)
-
Press ↵ Enter or ⏎ Return . Doing so will display the sum of all of the columns in your selected cell.
Advertisement
-
Click a cell below the column you want to add up. Doing so will place your cursor in the cell. This method uses the SUMIF function, which allows you to add values in a data range if they meet a certain criteria. [2] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
-
Enter the "SUMIF" function. Type =SUMIF() into the cell.
- SUMIF has three arguments: range, criteria, [sum_range]. The [sum_range] argument is optional and used to specify a different range to sum values in. The criteria argument will check the range specified in the first argument.
-
Enter the column's range and a comma. Type the top cell in the column, a colon, and the bottom cell in the column into the parentheses.
- For example, if you're checking the values in the A column and you have data in cells A2 through A10 , you would type in the following: =SUMIF(A2:A10,)
- If your dataset has a header row, don’t include the header in the SUM formula.
-
Enter the criteria you want to check. Place the criteria as the second argument after the comma. This can be a cell reference, text, number, expression, or function. Each cell in the specified range will be checked using this criteria. If the criteria is met, it will be added to the total sum. Here are a few examples:
- =SUMIF(A2:A10, 7) would check whether a value equals 7.
- =SUMIF(A2:A10, "<10") would check whether a value is less than 10. Note that logical criteria needs quotation marks.
- =SUMIF(A2:A10, "cat") would check whether the value is the word "cat".
-
Enter a comma and a sum range (optional). Place a comma after the criteria argument, then type a sum range. The sum range needs to be the same shape and size as the range in the first argument. SUMIF will check the criteria in the range and add the corresponding value in the sum range.
- For example, if you had 9 purchases in A2:A10 and their corresponding prices in B2:B10, you could sum the prices of purchase called "food" using this formula:
- =SUMIF(A2:A10, "food", B2:B10)
-
Press ↵ Enter or ⏎ Return . This will display the sum in your selected cell.
Advertisement
-
Click a column letter to see the sum of its values. This is the letter header located above the spreadsheet cells. The entire column will be highlighted.
-
Look at the status bar to see the sum. At the bottom of Excel is the status bar. The sum total of the values in the column will be displayed next to "sum:".
Advertisement
-
Select your dataset. Click and drag from the top left to the bottom right of your dataset to select it. This method changes the dataset into a table, to which you can add a total row. [3] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
-
Click the Home tab. It's at the top of the Excel window.
-
Click Format as Table . A drop down menu will appear with table options.
-
Select a table style. You can select from a variety of colors and formats. A small window titled "Create table" will appear after selecting a style.
-
Check the box next to "My table has headers" (if needed). If your dataset has a header row, select this option to format the table correctly. Otherwise, uncheck the box.
-
Click OK . This will change the dataset into a table.
-
Click the Table Design tab. It's at the top of the Excel window.
-
Check the box next to "Total Row". This is an option in the "Table Style Options" section of the Table Design tab. [4] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
-
Click the cell in the total row in the column you want to sum. A drop down arrow will appear next to the cell. For example, if your data is in A2:A10 and the total row is in row 11, you would click A11.
-
Click the drop down arrow ▼ . This will display a down down menu with total options.
-
Select Sum . This is an option in the total drop down menu. A sum total for that column will appear in the cell. You're done!
Advertisement
Community Q&A
Search
-
QuestionHow do I copy a formula?Community AnswerRight click on the cell with the formula, select "Copy," and then paste the formula into the desired cell. Keep in mind you may have to manipulate the formula in the new cell if the columns are not the same length.
-
QuestionHow do I multiply two cells?Community AnswerTo multiply two cells use the * key to multiply by pressing shift 8, and type it in the cell you want, e.g. A1*B5.
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
Advertisement
Video
Tips
- To total up a single column, you can enter the column's first value, a colon, and the last value into the SUM command. For example, to add cells A1, A2, A3, A4, and A5 together, you would type =SUM(A1:A5) into an empty cell.Thanks
-
Thanks
Submit a Tip
All tip submissions are carefully reviewed before being published
Name
Please provide your name and last initial
Thanks for submitting a tip for review!
Advertisement
Warnings
- Changing a column cell's contents will cause the cell in which you're displaying the columns' total to change its value as well.Thanks
Advertisement
References
- ↑ https://support.microsoft.com/en-us/office/use-autosum-to-sum-numbers-543941e7-e783-44ef-8317-7d1bb85fe706
- ↑ https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b
- ↑ https://support.microsoft.com/en-us/office/create-a-table-in-excel-bf0ce08b-d012-42ec-8ecf-a2259c9faf3f
- ↑ https://support.microsoft.com/en-us/office/add-a-total-row-to-a-table-9885a56c-51b5-487a-a168-054afd034631
About This Article
Thanks to all authors for creating a page that has been read 248,221 times.
Advertisement
