Google Sheets are currently a favorite in the spreadsheet market. In Google Sheets, you can sum numbers, cells, ranges, columns, or rows.

A lot of businesses use them to track, manipulate, and analyze data. They allow you to organize your data and do various operations and come with flexible sharing options.

Google Sheets provide various ways of getting a particular task done. There are usually at least 3 different approaches for any task.

However, the most common task while analyzing data is the need to sum numeric columns. You may need to show the total revenue, total cost for a product, a total number of items in your inventory, and the list goes on.

Spreadsheets are designed to allow you to quickly analyze big sets of data. A quick and easy way to do it is to find totals for numerical data.

A spreadsheet can instantly find the sum of a column of data, automatically updating that sum when data is changed or new data is added. There are a few different ways you can achieve this in a Google Sheets spreadsheet.

Let’s see how to sum a column using the SUM function in Google Sheets.

Sum a Column with the Status Bar

By default, the Status Bar appears at the bottom of your Google Sheet. If selected, it can show different summary statistics such as Count of Rows, Average, Sum, Minimum, Maximum, etc. The status bar gets activated as soon as you select a range in your sheet.

Select the data for which you want to show the sum through the Status Bar. In this case, the data range is C1:C10.

With the range selected, go to the lower right corner. You will see a dropdown with either the Sum, Average, Count, or similar statistics. Go ahead and tick the one you want as default. In this case, we want the SUM.

This is how we can sum a column with numbers through the Status Bar.

Sum a Column with Column Stats

Another way in which you can get meaningful insights and, specifically, the sum of numbers is by using the Column Stats.

This tool provides you with the summary statistics associated with a numeric column. You can get a quick idea about how many rows are empty, non-empty, Average values, Sums, Minimums, Maximum.

Moreover, it will also show you the distribution of your data.

To return a column sum through the Column Stats option, select the entire column of data.

This tool works in a way that once it is active, it stays active in the sidebar and returns the summary statistics for each column you select.

For now, select the range you want to have a column summary for.

• With the range selected, click on the Data tab from the menu to open up a variety of options associated with the selected data.
• Click on Column Stats to generate the summary statistics for the column.

As soon as you hit the option on the extreme right side of the sheet, you will see the Column stats window appearing, as shown in the screenshot below.

Scroll down a bit, and you can see the Sum of numbers inside the Column Stats window.

This, therefore, is another way of summing a column with numeric values.

Sum a Column with the Plus Operator

With this method, we use the plus operator as a way to get the sum of a column. The plus operator inside Google Sheets allows you to sum multiple cells from a numeric column.

The plus operator is an arithmetic operator that expects two or more inputs. Then the operator adds those numbers and returns the output.

To sum the column of numbers, navigate to any blank cell and insert the formula using the equal operator. In this example, let’s insert the formula in cell C12.

After the equals to (=) sign, input the first cell you want to sum. In this case, my first cell reference will be C2. After C2, add the plus operator inside the formula, and repeat this procedure for every other cell up to C10. Make sure to add the plus operator between each cell reference.

After the last cell, the plus operator required is not required.

Now, hit ENTER on the keyboard to get the results.

You can see the sum of cells C2:C10 inside cell C12, as shown above. This is how you can sum a numeric column using the plus operator.

A couple of things to take note of here.

• This method to sum the numbers is time-consuming. It becomes relatively impossible to use when you have data with thousands of rows in it.
• If your numbers are stored as text, it will convert them to numbers and return the total.

With that said, the plus operator is one of the easiest ways to create a running total for your data in Google Sheets.

Sum a Column with the SUM Function

The SUM function is the most used function in Google Sheets. This SUM function inside Google Sheets takes numeric cells as an argument and then returns the total. You can either separate each value argument with a comma, or select the entire range at once and add it as an input argument to the function.

Syntax for the SUM Function

SUM (value1, [value2, …])

• value1 – is a mandatory argument that specifies the first value to add. It can either be a single value, single cell, or a range.
• Value2 – is an optional argument, and can be a single value, a single cell or a range of cells.

In cell C12 of your sheet, insert the formula by typing =SUM(.

Input the first value as C2 inside the function. You can manually type it or go and click select cell C2. After that, add a comma so that you can provide other values in the sum. Add A3 as the second argument. Again, separate with a comma and add subsequent arguments as well.

= SUM (C2,C3,C4,C5,C6,C7,C8,C9,C10)

Finally, complete the formula by adding closing parentheses and hitting the Enter button. You will see the sum of the first six rows inside C12.

= SUM ( C2:C10 )

There is a better way of selecting these rows that is less painful.

Inside the SUM Function, click on cell C2 and drag the mouse cursor down until row C10 to select the range C2:C10 in a single shot.

This method is less painful than choosing each cell individually and separating it with a comma every time.

Sum a Column with the SUMIF Function

Google Sheets SUMIF and SUMIFS functions help analyze, organize, and sum data based on specific criteria.

For this example, we need the three-parameter version, which adds a “sum range.” That range is where Google Sheets will look when it matches the criterion. The sum range represents the cells to be totaled.

=SUMIF(range, criterion, sum range)

Sum a Column with the SUMIFs Function

The SUMIFS function is similar to SUMIF, except it allows multiple criteria. The parameters for SUMIFS are in a slightly different order.

=SUMIFS(sum range, criterion range, criterion)

It’s possible to have nearly unlimited criteria by adding pairs of criterion range and criterion.

=SUMIFS(sum range, criterion range 1, criterion 1, criterion range 2, criterion 2)

How to SUM a Column with the Function Toolbar

Another way of summing up a column of numbers is by using the Function Toolbar.

The Function Toolbar inside Google Sheets holds a variety of functions from different Function Libraries.

These libraries organize the functions by task type. For example, the Filter library holds all the functions that are useful for filtering your data. You’ll find all these libraries inside the Function Toolbar in Google Sheets.

We can use the Function Toolbar to insert the SUM function and return the total of your numeric column.

It is by far the easiest way of adding the SUM function inside your cells. Follow the steps below to generate a sum using the Function Toolbar.

As a first thing, select the cell where you want the sum to appear. I am choosing cell C12, where I would like to have a total of C2:C10.

Click on the Σ (Sigma icon, the Greek letter) dropdown to access the “Functions” menu with the cell selected.

Once you click on the dropdown, you will see the entire Functions Library. It has various function categories, and each of those has several functions that you can access.

You have two ways of accessing the SUM function.

• You can either see it and select in the upper side of the dropdown, where it shows the frequently used functions.
• Or you can navigate towards the Math library and from there you can select the SUM function.

Both these ways are valid, and you can see those through the screenshot above.

You can see that the SUM function is now active in cell C12.

= SUM (C2:C12)

Now, all you have to do is select a range on which you want to see the total.

In this example, click and drag to select the range C2:C12. Once done with the range selection, hit the ENTER button to execute the formula.

Sum a Column with the SUBTOTAL Function

All the above functions used so far are really very useful with any numeric column. However, they have their own limitations. You cannot use the SUM function if you want a total of filtered data. For such cases, it will fail.

To deal with data where filters are applied, and want to return the total of only the visible rows, then you need the SUBTOTAL function.

The SUBTOTAL function can be made to ignore any manually hidden rows or filtered rows from your dataset.

Analysts ideally use the SUBTOTAL function for three scenarios.

• To do different operations on the data, such as SUM, AVERAGE, COUNT, etc.
• To calculate different summary statistics for the filtered rows.
• Or, even to create dynamic summary statistics inside their report.

Here, just concentrate on the idea of making a sum using this function and then making a sum for the filtered data as well.

Syntax for the SUBTOTAL Function

SUBTOTAL ( code, range1, [range2, …] )

• code – A unique numeric code between 1 and 11 specifies the aggregation type used inside SUBTOTAL. It is a required argument, and in this case scenario, input the code argument as 9, since we are dealing with SUM.
• range1 – A first numeric range on which you want to perform the SUBTOTAL. Mandatory argument.

However, you can always include more than one range as an argument to work on this function.

Function_num	Function	Description
1	101	AVERAGE	Returns the average of numbers.
2	102	COUNT	Counts cells that contain numeric values.
3	103	COUNTA	Counts non-empty cells.
4	104	MAX	Returns the largest value.
5	105	MIN	Returns the smallest value.
6	106	PRODUCT	Calculates the product of cells.
7	107	STDEV	Returns the standard deviation of a population based on a sample of numbers.
8	108	STDEVP	Returns the standard deviation based on an entire population of numbers.
9	109	SUM	Add up the numbers.
10	110	VAR	Estimates the variance of a population based on a sample of numbers.
11	111	VARP	Estimates the variance of a population based on an entire population of numbers.

The list of codes for each function is as shown above.

Using the SUBTOTAL Function to Generate Sum for Filtered Data

Suppose you have data below, and want to show the Total Population for the table. At the bottom of the table inside cell C12, insert the SUBTOTAL function.

Since you wanted to aggregate the data using a sum, input the code argument as 9. Then, add range C2:C10 as the range1 argument.

= SUBTOTAL ( 9, C2:C10 )

Finally, use the closing parentheses to complete the formula and hit the Enter button to execute it.

The beauty of this function lies within its adaptiveness. If you apply a filter on the above range and then unselect a few rows, the SUBTOTAL function will only return the sum for selected rows.

The SUBTOTAL function omits those unselected cells from the calculation and only returns the total for filtered cells.

This is something you can’t achieve with the SUM function. Even if you filter the rows out, the function will still return the total based on all the cells.

Therefore, if you want to show a dynamic total to the users based on a filtered view, the SUBTOTAL is advised over the SUM function.

Using the SUBTOTAL Function to Generate SUM for the Hidden Rows

Now, consider a situation where a few rows are hidden from your data. In such cases, the conventional approach with the SUBTOTAL function doesn’t work.

See above, for example. Here, rows 5 to 7 are hidden, and the SUBTOTAL function still generates the entire sum rather than giving us the total for visible rows.

The screenshot shows the conventional code argument doesn’t work with hidden rows as it does with filtered rows.

To tackle this issue, there are three-digit codes that can help you omit the hidden rows from the calculations.

= SUBTOTAL ( 109, C2:C25 )

In the above illustration, I used code 109 instead of 9. The function ignores the hidden rows and generates a total for visible rows only.

The versatile SUBTOTAL function gives you two things.

• You can use it dynamically to show only the sum of filtered rows without altering the formula.
• You can also show the sum of visible cells when few cells are hidden out instead of filtered.

This function is much more powerful and flexible out of all the other ways discussed for generating sums.

Sum a Column Using the SUMPRODUCT Function

SUMPRODUCT is a powerful and versatile function from Google Sheets that allows you to do multiplication between each element of multiple arrays and then return the sum of that product.

This function is really unique as it can reduce the usage of two different functions for certain calculations (PRODUCT and SUM respectively).

However, this article is not about the various uses of SUMPRODUCT but different ways of returning the sum for a numeric column.

It turns out, you can use this function to generate only the sum for a given array.

Syntax for the SUMPRODUCT Function

SUMPRODUCT(array1, [array2, …])

• array1 – is a mandatory argument and specifies the first array for which the function returns an elementwise product with any other arrays listed and then sums up the result.
• array2 – is an optional argument. An array to be multiplied and summed. The system will multiply each element of this array (if specified) with each component of the first array.

Now, if you eliminate the second array or subsequent arrays from the SUMPRODUCT function, it will work exactly like the SUM function to return the total of a numeric column.

Suppose you have the same data for previous examples. There are three columns: Date, Courier Partner, and Net Value. This data specifies the total sales cost shipped through various couriers on specific dates.

Now, navigate down towards cell C12. Initiate the SUMPRODUCT function inside that cell.

Put C2:C12 as an array1 argument inside the function.

= SUMPRODUCT ( C2:C12 )

Now, you will intentionally ignore adding any other arrays inside the function thereby generating a sum.

Complete this formula by adding closing Parentheses. Then, hit the Enter button to execute this function.

The explanation for this one is quite simple.

You have intentionally ignored the array2 argument. It means the system doesn’t have an array to make elementwise multiplication with array1. Therefore, the system just adds those components from array1 and returns the output.

Sum a Column with the SERIESSUM Function

The SERIESSUM function comes from the mathematical and trigonometric library in Google Sheets. The function helps generate a power series sum.

SERIES(x, n, m, a) = a₁xⁿ + a₂x^(n+m) + a₃x^(n+2m) + … + a_ix^(n+[i-1]m)

The power series for which it generates the sum is as shown above.

Syntax for the SERIESSUM Function

SERIESSUM(x, n, m, a)

• x – is a mandatory argument that specifies the power series input value. This can be any real value number.
• n – It is a mandatory argument that allows the system to identify by what power it should raise the x inside the power series.
• m – a step by which you want to increase the initial power (n). It gets added into initial power for each part of the series.
• a – an array or range of coefficients that will be in multiplication with the input value of power series.

All four arguments are mandatory inside this function.

Now, you will discover you can use the SERIESSUM function to generate the total for an array using specific inputs.

Navigate towards the cell where you want to make the sum. In this example, the cell where you put the formula will be C12.

Now, inside cell C12, initiate the SERIESSUM function so that you can use it to generate the sum.

Since the function generates a sum for a power series, you have to provide arguments precisely here.

• The initial value (x) should be 1 so that it will not make a power series.
• The first power (n) should also be 1 so that elements of the series are not raised to a power.
• The secondary power should be zero. Even if you put something else for this value, there would be no effect as the initial value is 1.
• Finally, inside the array of coefficients, you should add the range C2:C10 for which you want the sum to be done.

= SERIESSUM ( 1, 1, 0, C2:C10 )

Finally, complete the formula by adding closing parentheses. Then, hit the Enter button to execute this function. You will see the sum of C2:C10 populated inside C12.

The result is a simple sum of the coefficients of the power series. In this case, a sum of the values in C2:C10.

Sum a Column with the DSUM Function

There is a database function named DSUM in Google Sheets which can be used to sum a column.

The DSUM function calculates the sum of a column with numbers based on a particular condition from a database-like array.

It works on similar lines to the traditional SUMIF function with some modifications.

With DSUM, you specify the entire dataset range, then set the field that needs to be summed, and finally, establish criteria based on which the sum happens.

Syntax for the DSUM Function

DSUM ( database, field, criteria )

• database – is a required argument that specifies an array or range structure resembling the SQL databases.
• field – a required argument that allows the system to decide on which column the aggregation should happen.
• criteria – a required argument which specifies an array or range with zero or more criteria mentioned based on which the conditional sum can be returned.

To make this function return the entire sum of a column, give it an entire range to work on, instead of providing a specific criteria.

Assume you want to have the Total Population based on the Countries as a criterion. Therefore, instead of using a specific cell as a criterion, use the entire column, and you will get the total.

Navigate towards cell C15. It is where you want to do the calculations. Insert the DSUM function inside that cell.

Now, provide the entire range from A1 to C13 as an input for the first argument (the database).

Since you wanted the sum of Population for the second argument, provide cell C1 as a value for it.

= DSUM ( A1:D13, C1, B1:B13 )

Finally, for the criteria argument, provide an entire column range B1:B13 as an argument. This is because you want the Total Population.

If you would like to have a total for the USA only, select a cell that contains the text and use it as a criteria argument.

Add closing parentheses and hit Enter button to complete this formula. You will see the Total Population.

This is how we can sum a column of numbers using the DSUM function.

Sum a Column with the QUERY Function

An advanced SQL-like code writing experience is enabled inside the Google Sheets using QUERY function.

There is a range of aggregation functions that you can use inside the QUERY function. You can use it to produce a COUNT, MIN, MAX, AVG, etc., to summarize your data.

We can use the SUM function inside the QUERY function to generate a total for a specific numeric column. In this case, the column will be the Net Value column.

Insert the QUERY function inside cell C15 from your sheet.

Provide the range A1:C13 as a data argument for the function. It is the data we want to apply aggregations on.

Now, for the query argument, use the SELECT SUM(C) argument inside the double quotes. Since you wanted the sum for Population in column C, you should mention the column reference using the SUM function inside the QUERY function.

= QUERY ( A1:C25, “SELECT SUM(C)” )

Close the parentheses to complete the formula, and hit the Enter button to execute it. You will get the sum for the Population column.

Sum a Column with a Pivot Table

Pivot tables are an integral part of a data analyst’s life, and throughout different blogs, we have been pretty vocal about this.

Data summarization is certainly easier with the help of pivot tables.

The tool has various value summarization methods such as Count, Min, Max, and Average. It’s no surprise there is also a Sum option.

To add a pivot table inside Google Sheet, select the entire data across A1:C13.

• Go to the Data menu and click on it to access the various options inside.
• Select the Pivot table option to add a pivot table for selected range of data.

As soon as you click on the Pivot table option, the Create pivot table window will appear.

Inside the Create pivot table window, you will see the data range A1:C13 automatically selected as a range for a pivot table.

Below that, two radio button options are associated explicitly for a location where the pivot table should be added.

Click on the Existing sheet radio button and select any cell from the current worksheet then hit the OK button.

Finally, click on the Create button to create a pivot.

Add the Population column inside the Values section of the pivot table through the Pivot table editor.

By default, the pivot table should use a SUM as an aggregator. But if you see a count, then this can easily be changed.

Now, under Summarize by section, click on the dropdown. You will see multiple aggregators associated with this column. Select the SUM option to return the Total Population.

Conclusion

Addition is one of the four basic operations in mathematics. You can add numbers together, or sum them, using a variety of methods. The easiest method is using the plus sign, similar to how we get to sum numbers in paper. Alternatively, the summation of numbers can also be accomplished through the use of SUM functions in the spreadsheet.

The Toolbar, Plus Operator, Status Bar, SUM functions are the standard and well-known methods of generating a sum. However, there are plenty of other non-standard options for summing that have been shown in this article. The SERIESSUM, DSUM, SUMPRODUCT are a few of those ways.

Hopefully, this post has helped expand your available toolset for the simple but invaluable sum. The SUM function is one of the simplest formulas to use in Google Sheets once you get the hang of it. And, when you’ve had enough Google Sheets sum of column practice, you’ll feel more comfortable tackling difficult formulas. There’s still plenty more to learn about spreadsheets.