One of the essential operations in Excel is summing numbers in a column.
So, for example, we can calculate a column to determine the total tuition fees paid in by students, the amount of taxes collected in a given period, the student population of a particular college, and so on.
Excel provides many ways of summing a column.
This tutorial uses practical examples to show some easy ways to sum a column in Excel.
Method #1: Use the Addition Operator to Sum a Column in Excel
One of the uses of the addition operator (+) in Excel is to add two or more cell values. Therefore, we can utilize the addition operator to sum a column in Excel.
Suppose we have the following dataset showing the names of students who signed up for various Excel courses and the tuition fees they paid.
We want to use the addition operator to compute the total fees paid by the students.
We use the below steps:
- Select cell C7 and type in the formula below:
=C2+C3+C4+C5+C6
- Press Enter.
The sum of the tuition fees paid by the students is displayed in cell C7.
Note: This formula is okay for small datasets such as the one used in this example, but it is tedious, inconvenient, and impractical for large datasets with tens or hundreds of rows. Imagine using the addition operator to sum a column with hundreds of rows! Therefore, we must consider applying the other methods explained in this tutorial.
Also read: How To Show The Total Row In Excel Table
Method #2: Use the SUM Function to Sum a Column in Excel
We can apply Excel’s SUM function that adds numbers in a cell range to sum a column.
We have the following example dataset showing students’ names, the Excel courses they signed up for, and the tuition fees they paid.
We want to use the SUM function to calculate the total tuition fees paid by the students and show the result in cell C7.
We use the following steps:
- Select cell C7 and type in the formula below:
=SUM(C2:C6)
- Press the Enter key.
The sum of column C is shown in cell C7.
Method #3: Use the AutoSum Button to Sum a Column in Excel
The AutoSum button is found in the Editing group of the Home tab. We can use the button to sum up a cell range in a worksheet quickly.
Let’s consider the following dataset showing the names of students who signed up for various Excel courses and the tuition fees they paid.
We use the AutoSum button to compute the total tuition fees paid by the students using the following steps:
- Select the cell range C2:C6.
- On the Home tab, in the Editing group, click the AutoSum button (The button with the Greek letter sigma icon).
The sum of the cell range C2:C6 is entered immediately in cell C7, below the selected range.
Method #4: Use the AutoSum Shortcut to Sum a Column in Excel
The AutoSum feature in Excel can be accessed using the AutoSum button as we did in Method #3.
The feature is also accessible through the Alt + = shortcut, which we apply in this method.
We have the following example dataset showing students’ names, the Excel courses they signed up for, and the tuition fees they paid.
We want to use the shortcut Alt + = to calculate the total fees paid by the students and display the result in cell C7.
We use the following steps:
- Select the cell range C2:C6.
- Press Alt + = (press and hold down the Alt key, press the = key, and then release the Alt key)
The sum of the tuition fees paid by the students is immediately displayed in cell C7, below the selected cell range.
Method #5: Use the Excel Status Bar to Sum a Column in Excel
Excel’s status bar feature is at the bottom of the Excel window.
The status bar reveals information about the current state of the active sheet, such as the sum of the selected cells.
Therefore, we can use this feature to calculate the sum of a column in Excel.
Let’s consider the following dataset showing the names of students who signed up for various Excel courses and the tuition fees they paid.
We want to use the status bar to calculate the sum of the tuition fees paid by the students.
We use the following steps:
- Select the range C2:C6 that we want to sum.
Notice that the sum of the selected range is displayed towards the right end of the status bar.
Note: When we select the entire column C by clicking the column header, we still get the same result on the status bar because the status bar feature only accounts for the cells with numbers and ignores those with text values.
Excel does not have a provision for us to select the sum statistic on the status bar and copy and paste it into cell C7.
Therefore, if we want to show the sum value in cell C7, we must memorize or jot the sum value somewhere and manually type the number in cell C7.
Note: If you don’t see the sum statistic on the status bar, right-click the status bar and select the Sum option on the Customize Status Bar feature that appears.
Method #6: Apply the SUBTOTAL Function With Code 9 to SUM a Filtered Column in Excel
A filtered column in Excel refers to a column that shows only particular data based on one or more conditions applied using the Filter feature.
The Filter feature allows us to temporarily hide rows that do not satisfy the conditions we set so that we can focus on the most relevant data.
Filtered columns have drop-down arrows in their headers.
To calculate the sum of a filtered column in Excel, we must use the SUBTOTAL function with code 9.
If we use the SUM function, we get an incorrect result. For example, in the dataset below, rows 4, 5, and 6 are filtered.
Using the SUM function in cell C7 returns the wrong figure, 18,000.00, which includes the values in the filtered rows.
Consequently, we must use the SUBTOTAL function with code 9 to get the correct sum of the filtered column.
The SUBTOTAL function with code 9 returns a subtotal in a dataset that ignores the values in filtered rows.
To compute the sum of our filtered example dataset, we use the following steps:
- Select cell C7 and type in the below formula:
=SUBTOTAL(9,C2:C6)
- Press Enter.
The correct subtotal value of 7,100.00 is displayed in cell C7.
Method #7: Use the SUBTOTAL Function With Code 109 to Sum Column With Hidden Rows in Excel
Hidden rows in Excel are those not displayed in the worksheet but are still in the worksheet.
To compute the sum of a column with hidden rows, we must use the SUBTOTAL function with code 109.
The SUBTOTAL function with code 109 returns a subtotal in a dataset that ignores the values in hidden rows.
If we use the SUBTOTAL function with code 9 the way we did in Method #6, we get an incorrect result. For example, in the dataset below, rows 3 and 6 are hidden.
If we use the SUBTOTAL function with code 9 in cell C7, we get the wrong result of 18,000.00, which includes the values in the hidden rows.
To determine the correct sum of column D that excludes the values in the hidden rows, we use the SUBTOTAL function with code 109, as shown below:
- Select cell C7 and type in the formula below:
=SUBTOTAL(109,C2:C6)
- Press Enter.
The correct subtotal value of 11,200.00 is shown in cell C7.
Method #8: Use the AGGREGATE Function With Code 9 and 3 to Sum a Column With Filtered and Hidden Rows in Excel
The AGGREGATE function calculates an aggregate in a dataset.
For example, we can use the AGGREGATE function with codes 9 and 3 to sum a column with filtered and hidden rows in Excel.
We use code 9 because it defines the SUM aggregate we want to calculate.
Code 3 defines the broadest possible combination of values we want the AGGREGATE function to ignore, which includes values in filtered and hidden rows.
Sum a Column With Filtered Rows Using the AGGREGATE Function
We use our example dataset below that has filtered rows to explain how we can use the AGGREGATE function with codes 9 and 3 to sum a column with filtered rows.
We want to sum column C using the AGGREGATE function with codes 9 and 3 and display the result in cell C7.
We use the following steps:
- Select cell C7 and type in the formula below:
=AGGREGATE(9,3,C2:C7)
- Press Enter.
The correct sum of only the visible cells in column C is reflected in cell C7.
Sum a Column With Hidden Rows Using the AGGREGATE Function
We use our example dataset below that has hidden rows to show how we can use the AGGREGATE function with codes 9 and 3 to sum a column with hidden rows.
We want to sum column C using the AGGREGATE function with codes 9 and 3 and display the result in cell C7.
We use the following steps:
- Select cell C7 and type in the formula below:
=AGGREGATE(9,3,C2:C7)
- Press Enter.
The correct sum of only the visible cells in column C is displayed in cell C7.
Method #9: Use the SUMPRODUCT Function to Sum a Column in Excel
The SUMPRODUCT function computes the sum of the products of two or more corresponding cell ranges or arrays.
The function takes one required array1 argument and optional array arguments 2 to 255.
We can use the SUMPRODUCT function to sum a column in Excel.
This method makes the SUMPRODUCT function work like the SUM function by specifying only the first mandatory array argument and not the optional arguments.
We use our example dataset below to explain how the SUMPRODUCT function can calculate the sum of a column in Excel.
We want to use the SUMPRODUCT function to sum column C and display the outcome value in cell C7.
We proceed as follows:
- Select the cell C7 and type in the formula below:
=SUMPRODUCT(C2:C7)
- Press Enter.
The sum of column C is displayed in cell C7.
Note: Without any optional 2 to 255 arguments, the SUMPRODUCT function cannot make elementwise multiplication with the required array1 and, therefore, the function only adds the elements of array1 and returns the result.
Method #10: Use the SERIESSUM Function to Sum a Column in Excel
The SERIESSUM function in Excel is used to calculate the sum of power series based on the formula given below:
The SERIESSUM function takes the following four mandatory arguments:
- X This argument specifies the input value we want to calculate the sum of the series.
- N This argument identifies the power to which we wish to raise X in each series term.
- M This argument specifies the step to increase the starting power N.
- Coefficients (A) This range or array of coefficients is to be multiplied with the power series input values.
We use our example dataset below to demonstrate applying the SERIESSUM function to sum a column in Excel.
We use the SERIESSUM function to sum column C and display the result in cell C7.
We use the steps below:
- Select cell C7 and type in the formula below:
=SERIESSUM(1,1,0,C2:C7)
Note: The first argument (X) is 1, so the formula doesn’t make a power series. The second argument (N) is 1, so the elements in the series are raised to the power of one, and the third argument (M) is zero but can be any number because the initial argument X is 1.
- Press Enter.
The sum of column C is shown in cell C7.
Method #11: Use the DSUM Function to Sum a Column in Excel
The DSUM function adds the numbers in the field or column of records that meet the specified conditions in a table-like range.
For example, we can use the DSUM function to sum a column in Excel.
The DSUM function takes three mandatory or required arguments:
- Database This argument specifies an array or table-like range.
- Field This argument identifies the column to sum.
- Criteria This argument indicates the range of cells containing the specified conditions.
We use our example dataset below to demonstrate the use of the DSUM function in summing a column in Excel.
We want to use the DSUM function to sum the values in column C and display the result in cell C7.
We use the below steps:
- Select cell C7 and type in the following formula:
=DSUM(A1:C6,C1,A1:A6)
Note: Because we want to calculate the total sum of column C, we don’t use a specific cell in column A but the whole column A as the criteria.
- Press Enter.
The sum of column D is displayed in cell C7.
Method #12: Use a PivotTable to Sum a Column in Excel
A PivotTable is an Excel tool that allows us to summarize and analyze large amounts of data. For example, we can use a PivotTable to sum a column in Excel.
We use the following dataset to show how to use a PivotTable to sum a column in Excel.
We use the steps below:
- Select any cell in the dataset, open the Insert tab, and click the PivotTable button in the Tables group.
- On the PivotTable from table or range dialog box that appears, we use the range selectors (with upward arrow icons) to select the entire dataset and to choose where we want the PivotTable to be placed, as shown below and click OK.
On the PivotTable Fields pane that appears, select the Tuition Fee (USD) field and notice that the text “Sum of Tuition Fee (USD)” appears in the Values area of the pane because, by default, the PivotTable uses the SUM aggregator.
The sum of column C is displayed in cell G3.
Also read: How to Count Columns Using VBA
Method #13: Sum a Column in Excel By Converting the Data Range to an Excel Table and Adding a Total Row
When we convert a data range to an Excel table, we can add a total row showing the sum of a column.
Suppose we have the following dataset showing the names of students who signed up for various Excel courses and the tuition fees they paid.
We want to convert the data range to an Excel table and add a total row showing the sum of column C.
Convert the Data Range to an Excel Table
To change the data range to a table, we follow the steps below:
- Select any cell in the data range and press Ctrl + T.
- Click the OK button on the Create Table dialog box that appears. Notice that Excel has correctly guessed the dimensions of the data range and the fact that it has headers. If the guess is incorrect, we can make appropriate adjustments.
The following Excel table is created.
Compute the Sum of Column C
To calculate the sum of column C of the Excel table, we use the below steps:
- Select any cell in the Excel table.
- On the contextual Table Design tab, in the Table Style Options group, select the Total Row option.
The total row is added to the table’s end, and column C’s sum is shown in cell C7.
This tutorial showed thirteen techniques for summing a column in Excel. We hope you found the tutorial helpful.
Other Excel articles you may also like:
