How to sum column values in Google Sheets
Google Sheets is probably the most handy and widely used online software tool to manipulate and analyse numeric data. It has many functions and tools to do all calculations ranging from simple to complex level.
When we talk about adding a whole column in Google Sheets or Excel then it could mean two things.
- Sum all the values in a particular column
- Add or insert a new column in the sheet
In today’s tutorial we’ll learn both methods one by one.
Case 1: Sum all the values of a particular column
This is probably the most common requirement and most frequently used tool of Google Sheets. So let’s learn it how to add all values contained in a cell by using the following methods;
- Using Auto-Sum from Editing Group on Home Tab or Auto-Sum Shortcut Key
(Add successive values of the same column only)
- Writing a formula to get the sum of adjacent or non-adjacent values present anywhere in the sheet
Consider the following data set containing the sales data from various cities of various products.
Method 1 – Using AutoSum to add a column (sum all values)
Using this method we can add the values in the column very quickly and without much effort. However, the downside of using this tool is that we can only add the values which are adjacent to each other in a column. If there are non-numeric values in between the numeric values then this method won’t help you. We’ll take care of this issue in the second method.
So to use the first method follow along these steps and calculate the total sales.
Step 1 – Use Auto-Sum Option
- Select all the adjacent cells in the column that you want to add.
- On the Menu bar go to the extreme right and click on sigma ∑ icon Sum and then click on the SUM option.
- Google will write the formula to sum all above values of the column automatically. You just need to press enter to finalize the calculation. The sum of all the values present above will be calculated then.
Method 2 – Write a Formula to add values (Adjacent or Nonadjacent)
By writing a formula ourselves we have the liberty to choose any cells that we wish to add. Let’s use the same data set and calculate the total marks of some of the subjects only by following the steps mentioned below;
Step 1 – Write the formula in an appropriate cell and choose cells
- Write the basic formula to add values i.e.
- In Google Sheets, as soon as you write the formula, it senses that you wish to get the sum and it autofills the formula with most probable values. However, you can change them as per your wish.
- Leave the inside blank for now. However, click once in between the parentheses so that we can add cell addresses to sum.
- If you wish to add the adjacent values only then select the first one and drag down all the way to the last value.
- If you wish to select the non adjacent values then after selecting the first cell, hold on CTRL key and keep on clicking all those cells you wish to sum. When you are done with selecting cells just press enter. This way you will get the sum of only those cells which you selected. So this method gives us the liberty of choosing any cells that we wish to add.