Skip to Content

12 Ways to Sum a Column in Google Sheets

Google Sheets is a web-based spreadsheet program with many features, such as adding all or some of the numbers in a column.

So, for example, we can use the ability to add all or a subset of numeric values in a column to determine the total cost of a product, revenue, number of products in the inventory, and so on.

Google Sheets provides various ways of summing a column. This tutorial shows twelve techniques for determining the sum of a numeric column in Google Sheets.

Without any further ado, let’s dive into it.

Method #1: Sum a Column in Google Sheets Using the SUM Function

Google Sheets has a built-in SUM function that we can use to add all the numbers in a column.

Let’s consider the following dataset showing the description, quantity, price, and line totals of various computers bought by a particular organization.

We want to use the SUM function to calculate the total of the line totals in column D and display the result in cell D9.

We use the following steps to sum column D:

  1. Select cell D9 and type in the formula below:

=SUM(D2:D8)

Note: A formula in Google Sheets always begins with an equal (=) sign. The equal sign allows Google Sheets to interpret the characters that follow it as a formula.

As you begin to type in the formula, Google Sheets’ built-in intelligence senses what you want to do and gives you an autocomplete suggestions.

 Sum a Column in Google Sheets Using the SUM Function

If the autocomplete suggestion is correct, press the tab key to accept it, and immediately the total is displayed in cell D9.

Note: If the autocomplete suggestion is incorrect, ignore it and finish typing in the formula, then press the Enter key. The total is suddenly displayed in cell D9.

Method #2: Sum a Column in Google Sheets by Choosing the SUM Function on the Functions Drop-down

Google Sheets has a Functions drop-down feature on the toolbar that we can apply in summing a column.

Suppose we have the following dataset showing the description, quantity, price, and line totals of various computers bought by a particular company.

We want to compute the total in column D using the SUM function option on the Functions drop-down and display the value in cell D9.

We use the steps below:

  • Select the cell range D2:D8 that we want to sum.
  • Open the Functions drop-down on the toolbar (the drop-down control has a sigma icon), and select the SUM function.
Sum a Column in Google Sheets by Choosing the SUM Function on the Functions Drop-down

The functions feature enters a sum formula in cell D9:

  • Press the Enter key or the Tab key to enter the formula, and the total is displayed suddenly in cell D9.

Method #3: Sum a Column in Google Sheets Using the Plus (+) Operator

The Plus or the Addition operator in Google Sheets adds two or more numeric values and returns a numeric value.

We have the following dataset showing the description, quantity, price, and line totals of various computers bought by a particular firm.

We want to use the plus or addition operator to calculate the total in column D.

We use the following steps:

  • Select cell D9 and type in the below formula:

=D2+D3+D4+D5+D6+D7+D8

Sum a Column in Google Sheets Using the Plus (+) Operator
  • Press Enter.

The grand total is shown immediately in cell D9.

This method is okay for small datasets but is inconvenient for large ones with tens or hundreds of records.

Method #4: Sum a Column in Google Sheets Using the Status Bar

The Google Sheet’s Status bar feature is at the bottom of the Google Sheets window. The status displays information about the current sheet, such as the sum of the selected cells. For example, we can use this feature to determine the sum of a column in Google Sheets.

We will use the example dataset to show how the status bar can sum a column in Google sheets.

We want to determine the total of the line totals in column D and enter the result in cell D9.

We use the following steps:

  • Select the range D2:D8 that we want to sum.

Notice the statistics control that becomes active on the right end of the status bar.

  • Click the down arrow on the statistics control and select Sum on the list that appears:

The sum of the selected cell range D2:D8 is displayed in the statistics control:

Note: Selecting the whole column would still yield the same result because the SUM summary function on the status bar only takes account of the cells with values.

  • Jot the value displayed in the statistics control somewhere and type it into cell D9.

Method #5: Use the SUBTOTAL Function With Code 9 to SUM a Filtered Column in Google Sheets

If you want to calculate the sum of a filtered column, use the SUBTOTAL function with code 9. If you use the SUM function, it won’t work. For example, in the dataset below, rows 2, 3, and 5 are filtered.  

Using the SUM function in cell D9 returns figure 10730.88, which includes the values in the filtered rows.

In this case, 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 specific subtotal in a dataset.

To determine the subtotal of our filtered example dataset, we use the following steps:

  • Select cell D9 and type in the below formula:

=SUBTOTAL(9,D4:D6)

Note: Code 9 argument specifies the SUM aggregation type that ignores filtered rows to be used by the SUBTOTAL function.

  • Press Enter.

The correct sum subtotal value of 1545 is displayed in cell D9.

Method #6: Use the SUBTOTAL Function With Code 109 to Sum Column With Hidden Rows in Google Sheets

If you want to calculate the sum of a column with hidden rows, use the SUBTOTAL function with code 109. However, if we use the SUBTOTAL function with code 9 the way we did in method #5, it will return an incorrect result. For example, in the dataset below, rows 2, 3, and 4 are hidden. 

Using the SUBTOTAL function with code 9 in cell D9 returns figure 10730.88, which includes the values in the hidden rows.

To determine the sum subtotal of column D that excludes the values in hidden rows, we use the below steps:

  1. Select cell D9 and type in the formula below:

=SUBTOTAL(109,D2:D8)

Note: Code 109 specifies the SUM aggregation type that ignores hidden rows to be used by the SUBTOTAL function.

  • Press Enter.

The correct subtotal value of 6600.97 is shown in cell D9.

Method #7: Use the Column Stats Option on the Data Menu to Sum a Column in Google Sheets

We can use the Google Sheets’ Column stats option on the Data menu to sum a column.

We use our example dataset below to show how we can use the Column stats option to sum a column in Google Sheets.

We want to sum column D using the column stats feature and display the result in cell D9.

We use the steps below:

  • Select the range D2:D8 which contains the data we want to sum.
  • Open the Data menu on the menu bar and choose the Column stats option.

The Column Stats pane appears on the right of the Google Sheets window.

  • Scroll down the Column Stats pane and notice the sum aggregate towards the bottom of the Column Stats pane.
  • Refer to the Column Stats pane, type the sum aggregate value into cell D9, and press Enter.

Notice that the Column Stats pane remains open as you type the value in cell D9.

Method #8: Use the SUMPRODUCT Function to Sum a Column in Google Sheets

We can use the SUMPRODUCT function to sum a column in Google Sheets. The SUMPRODUCT function caculates the sum of the products of two corresponding cell ranges or arrays. The function takes two arguments, one mandatory array and the other optional.

This method makes the SUMPRODUCT function work like the SUM function by specifying only the first mandatory array and not the second optional one.

We use our example dataset below to explain how the SUMPRODUCT function can sum a column in Google Sheets.

We want to sum column D and display the outcome value in cell D9.

We proceed as follows:

  1. Select cell D9 and type in the formula below:

=SUMPRODUCT(D2:D8)

  • Press Enter.

The sum of the cell range D2:D8 is displayed in cell D9.

Note: In the absence of the second optional argument for the SUMPRODUCT function, the function cannot make elementwise multiplication with the specified array1 and, therefore, adds the elements of array1 and returns the result.

Method #9: Use the SERIESSUM Function to Sum a Column in Google Sheets

We can use the SERIESSUM function to sum a column in Google Sheets. The SERIESSUM function computes the sum of a power series based on the formula shown below.

The SERIESSUM function takes four mandatory arguments:

  •  specifies the power series input number.
  • identifies what power the x argument should be raised to.
  • specifies the step by which to increase the starting power n.
  • is a range or array of coefficients 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 Google Sheets.

We will use the SERIESSUM function to sum the range D2:D8 and display the result in cell D9.

We use the following steps:

  • Select cell D9 and type in the formula below:

=SERIESSUM(1,1,0,D2:D8)

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 result of the SUMSERIES function is shown in cell D9.

Method #10: Use the DSUM Function to Sum a Column in Google Sheets

We can use the DSUM function to sum a column in Google Sheets. The DSUM function adds the numbers in the field or column of records in a table-like range that match specified conditions.

The DSUM function takes three mandatory or required arguments:

  • Database  This is an array or table-like range.          
  • Feld  This is the column to sum.
  • Criteria  This is 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 Google Sheets.

We want to use the DSUM function to sum the values in column D and display the result in cell D9.

We use the following steps:

  • Select cell D9 and type in the following formula:

=DSUM(A1:D8,D1,A1:A8)

Note: Because we want to determine the total sum of column D, we don’t use a specific cell in column A but the entire column A as the criteria.

  • Press Enter.

The sum of column D is displayed in cell D9.

Method #11: Use a Combination of QUERY and SUM Functions to Sum a Column in Google Sheets

We can combine QUERY and SUM functions to sum a column in Google Sheets. The QUERY function sorts and filters data in a table-like range based on certain conditions, and the SUM function adds all the numbers in a range of cells.

The QUERY function has one optional argument and two required arguments:

  • Data  This required argument is the cell range to which we want to apply the function.
  • Query  This mandatory argument specifies the way to manipulate the data.
  • Headers  This optional argument shows how many title rows the data has.

We use the dataset below to explain how we can combine the QUERY and SUM functions to sum a column in Google Sheets.

We want to combine QUERY and SUM functions to sum column D and show the result in cell F5.

We use the below steps:

  • Select cell F4 and type in the below formula:

=QUERY(A1:D8,”SELECT SUM(D)”)

  • Press Enter.

The sum of column D is shown in cell F5.

Method #12: Use a Pivot Table to Sum a Column in Google Sheets

We can use a Pivot table to Sum a Column in Google Sheets. A Pivot table is a Google Sheets tool that allows us to summarize and analyze large amounts of data.

We use the following dataset to show how we can use a Pivot table to sum a column in Google Sheets.

We use the steps below:

  • Select the entire dataset, open the Insert menu and choose the Pivot table option.
  • On the Create Pivot table dialog box that appears, select the Existing sheet option, use the data range selector to select any empty cell on the worksheet, and click the Create button.
  • On the Pivot table editor that appears, click the Add button in the Values section and select Line Total (USD) on the list that shows up.

By default, the Pivot table uses the SUM aggregator.

The sum of column D is displayed in the cell we selected in the Create Pivot table dialog box.

Conclusion

This tutorial showed twelve techniques for summing a column in Google Sheets.

We hope you found it helpful.

Other Google Sheets Resources You May Find Useful