Come on, open your spreadsheet and find out how to sum a column in Excel thanks to the guidance you are about to read. In no time, we will discover how to sum all the values in a column, how to sum values across two or more columns, and how to calculate an average of values from a series of cells. I assure you these are all simple operations, just roll up your sleeves and get to work.
I have used Office 2024 for this tutorial (which is available for both Windows and macOS), but the instructions provided can be applied to all recent versions of the Microsoft suite, including Excel Online and the Excel app for smartphones and tablets. With that said, I wish you a good reading and good luck!
Table of Contents
- How to Sum a Column in Excel from PC
- How to Sum Values of Multiple Columns in Excel
- How to Sum a Column in Excel with Filter
- How to Sum Columns in Excel Using Structure
- How to Sum a Column in Excel Online
- How to Sum a Column in Excel from Smartphone and Tablet
How to Sum a Column in Excel from PC
To sum the data in a column in Excel, you can use the SUM function. In the upcoming sections, I will show you how to perform this operation, both for an entire column and for a specific range of data.
How to Sum an Entire Column in Excel
If you want to learn how to sum a column in Excel, after opening the workbook, all you need to do is select the cell where you want to display the result of the operation and click the AutoSum button (the icon with the uppercase sigma Σ) located in the Home tab of the program’s toolbar.
At this point, you have two options: highlight the entire column or only the range of data that contains all the information. Let’s see how to proceed.
In the first case, to sum an entire column in Excel, after activating AutoSum, you need to enter the column reference as the argument of this function by clicking on the column header, which is its letter. For example, if your data is in column A, simply click on the A label of that column to get the formula =SUM(A:A). Press the Enter key to view the result of the calculation.
You can instead sum a column of numbers in Excel by restricting this operation to the range from the first to the last value of that column, thus not affecting empty cells (which may contain other types of data).
After activating AutoSum, just click on the first cell, hold down the left mouse button, and drag down with the cursor to the last cell. For example, to include in the sum all the data from cell A1 to A8, type the formula =SUM(A1:A8) and press the Enter key on your keyboard.
How to Sum Column Elements in Excel
If your intention is to sum only certain elements of a column in Excel, the operation is not much different from what was seen earlier. Therefore, after opening the spreadsheet, locate the cell where you want the summation to take place and then click on the AutoSum button (the symbol Σ) found in the Home tab.
Once done, to include only specific cells in the SUM function, hold down the Ctrl key (on Windows) or cmd (on Mac) and click on all the cells in the column that should be included in the sum. When you’re done, hit the Enter key on your keyboard to see the result of the calculation.
How to Sum Some Values of a Column in Excel
In Excel, you can set up the automatic summation of column values only if they meet specific criteria. However, the function to use will not be SUM, but rather SUMIF or SUMIFS, which allow you to include one condition or multiple conditions, respectively.
To use these functions, open the spreadsheet and select the cell where you want the calculation to be displayed. Inside the box, if you want to impose a single condition, type the formula =SUMIF([range];[condition]). Instead of [range], you should insert the column or range of the column’s data, while for [condition], you must indicate the rule to impose to filter the data to sum.
For example, if your data is in column A, specifically in the range A1:A8, and you want to know the sum of only those numbers greater than 5, the formula you should type will be =SUMIF(A1:A8;">5"), which in my case returns the value 16.
If you want to set more conditions on a complex table, the perfect function is SUMIFS, which works in a very similar way to the one just illustrated. Its generic function requires these arguments =SUMIFS([range];[criteria_range1][criteria1],[...]).
Instead of [range], you should indicate the column or range for which the sum should operate, while [criteria1] and [criteria_range1] denote the condition to apply and the range on which the condition’s analysis should occur. Note that you can apply multiple conditions by entering the analysis range and condition as successive arguments.
Consider a table of values arranged in two columns: column A refers to data whose corresponding values are in column B and it’s this latter column where you want to perform the summation. The condition is that the data in column A meet a specific value (in my case, that they correspond to ‘July’).
Now, choose a cell where you want the calculation to occur, use the formula =SUMIFS(B1:B8;A1:A8;"July"), and press the Enter key on your keyboard. In this way, the sum will be performed only for the values in the range B1:B8, analyzing the condition that the values in A1:A8 must be equal to ‘July’. It wasn’t difficult, was it?
How to Sum Values of Multiple Columns in Excel
You don’t have to limit yourself to summing a column in Excel but need to sum the total of two or more columns in the spreadsheet? Just use the same technique as before, albeit with a slight variation.
If you want to use the AutoSum function, you must select the cell where the result of the operation will be displayed, click the AutoSum button, and select the columns to add. To select distant columns, for example, A and C, you must hold down the Ctrl key on the keyboard (cmd on Mac) while clicking on their titles.
If you prefer formulas instead, click on the cell where you want the result to be displayed and type =SUM(A:B), where A and B are the letters of the columns to sum. Easy, right?
How to Sum a Column in Excel with Filter
Sometimes it might be useful for the cell showing the total of a column to reflect exactly the filtered data. You should know, however, that using the methods I’ve outlined in the preceding chapters on a table will give you the precise calculation result regardless of whether you have applied any filters.
If, however, you need this total to change with each instance based on the table’s filter, what you have to do is use the SUBTOTAL() function. This accepts two arguments: the first is mandatory and equals 9, which indicates that a sum must be performed, while the second is the range of cells or the entire column.
For example, if you have a series of data between the range B2 and B7, in a cell type the formula =SUBTOTAL(9;B2:B7). This way, applying a visual filter (to the entries Sort and Filter > Filter present in the Home tab) will change the total based on the displayed data.
How to Sum Columns in Excel Using Structure
If your spreadsheet contains figures corresponding to certain values (e.g., the price at which you sold used games), you can group these to obtain subtotals. Subtotals refer to partial totals corresponding only to specific values (e.g., only games for PS4 or PC). This procedure, however, can only be used in the desktop version of Microsoft Excel, thus excluding Excel Online and the Excel app for smartphones and tablets.
To insert subtotals in your spreadsheet, select all the columns you want to use in the calculation (in my example, those with the titles of games, their target platforms, and the prices at which they were sold), select the Data tab in Excel, and click the Structure > Subtotal button located at the top right.
In the window that opens, use the dropdown menu At each change in to specify the values based on which Excel should create subtotals (in my example, the target platforms of the games), check the box next to the column containing the figures to sum (e.g., Price), and click the OK button.
Subtotals relating to the values you selected will automatically appear in the spreadsheet (in my example, the games for PS4 and those for PC) and the total related to all values present in the selected columns (thus, the total price of PS4 games plus the price of PC games, to stick to my example).
How to Sum a Column in Excel Online
If you do not have the desktop version of Excel but use the free platform of Excel Online, know that all the procedures I’ve indicated in this previous chapter are valid.
Therefore, you can use the SUM function, as well as the SUMIF and SUMIFS functions. What I recommend is to read carefully the instructions I provided in the previous paragraphs, so that you can apply the sum on your data, using the function that best fits your needs.
How to Sum a Column in Excel from Smartphone and Tablet
Also on smartphones and tablets running Android or iOS/iPadOS, using the Excel app, you can perform summation operations on columns. I remind you that the Excel app is completely free for devices with screens no larger than 10.1 inches; for larger sizes, a subscription to Microsoft 365 is required.
To achieve this goal, you can perform the same operations I indicated in the previous chapters. Keep in mind that the SUM function can easily be called from the Home tab of the Excel app, which you find at the top if using a tablet, while on smartphones, this tab appears as an item in the dropdown menu at the bottom.