Let’s imagine that you run a small coffee shop and are required to keep track of your daily sales on Google Sheets workbook. You have a column where you enter the money made daily. This column is labeled “Daily Sales” in column B. You add the sales amount in a new row under this column daily.
Basic SUM Formula to Sum a Column
In Google Sheets, like all other spreadsheet applications, you have the SUM function to sum values from a column or a row.
In simple words, SUM adds up numbers from a range of cells. The syntax of SUM is simple; you need to enter the function and then refer to the range that you have in the column to add to the result.
To use this, let’s say you have sales amounts in cells B2 to B11; you can type =SUM(B2:B11) in cell B12 to get the total sales numbers.
Here’s an awesome tip: when you type SUM in cell B12 to sum the values from the column above, Google Sheets is smart enough to know that you want to sum values from the cells above, giving a predefined formula in the list.
See the above example; I have only typed “Su”, which has shown me the sum formula with the range from the above cells. This is amazing. I love how Google Sheets is getting good at understanding its users. So here, you need to press the tab to enter the formula. Type “Su” and enter the tab to enter the formula; yes, that’s it.
But, if you want to get the sum of the column in a different cell or other than the cell below the values, in that case, you need to type the formula manually.
Note – Sum only adds values from the cells with the numeric values.
Sum an Entire Column
To sum up an entire column in Google Sheets, you can write the formula in the following way. If you want to sum all the values in column B, use the formula =SUM(B:B). This will add up to all the numbers in column B, including any future entries you might add.
To enter this formula, click on the cell where you want the total to appear, for example, cell B1, and then type =SUM(B:B) and press Enter. Writing a sum formula this way is helpful when you have a long list of numbers and want to continuously update the total as you add new entries to the column.
And yes, you can refer to multiple columns in the same formula to get the sum in the cell.
Note – In Google Sheets, the SUM function has no problem if the columns you specify have one or more merged cells.
Use New Table Feature to Sum a Column
In Google Sheets, a new feature was added in June 2024: “Convert to Table.” When you apply it to your data, it converts that data into a table, and with that table, you can use structured cell references. To convert a data into a table, go to the Format > Convert to Table
After that, select the cells in the columns below the cells you want to sum. Enter the SUM function, and then refer to the range above.
In the above example, you can see that we have used the SUM and structured the reference in the formula to sum the column.
Enter SUM from the Toolbar
If you don’t want to type, use the toolbar function button to use the enter SUM function in a cell. For this, click on the cell where you want the total to appear, for example, cell B12. Then, go to the toolbar at the top of the Google Sheets interface.
After that, click the “Functions” button (it looks like a Greek letter Sigma ∑). From the drop-down menu, select “SUM”. Google Sheets will automatically suggest a range of cells to sum. If this range is correct, press Enter. If it’s not, you can adjust the range.
SUM Column Based on a Criteria
Now, from the list of daily sales amounts for your coffee shop in column B, you want to find out the total sales for days when the sales amount was greater than 150. In this case, you need to use SUMIF. SUMIF helps you sum up only those numbers in a range that meet a specific condition.
Click on the cell where you want to get the total sales amount, for example, B12. In the selected cell (B12), type the following formula.
=SUMIF(B2:B11,">150")
Now, when you hit enter, it returns the sum of the sales from the column, which is (>=) greater than or equal to 150.
In this formula, you have specified B2:B11, the range of cells where your daily sales amounts are listed. You have used the criteria>150, which is the condition that specifies that you only want to sum the sales amounts that are greater than 150.
Dynamic Range Sum
Earlier, we learned that you could refer to an entire column to create a formula that can help you sum values when you enter them in the future in the column. But you can also write a sum formula for a sum column using a dynamic range.
To create a dynamic formula that extends the range, you need to combine SUM, COUNTA & INDIRECT.
=SUM(B2:INDIRECT("B" & COUNTA(B:B))) The formula dynamically sums column B from B2 to the last non-empty cell. COUNTA(B:B) counts all non-empty cells in column B, and INDIRECT(“B” & COUNTA(B:B)) creates a reference to the last non-empty cell.
Sum Every Other Cell in the Column
Let’s say you want to SUM the sales amounts in column B for every other row, specifically the even-numbered rows. In this case, you can use the formula like below:
=SUM(FILTER(B2:B11, MOD(ROW(B2:B11), 2) = 0))
The ROW(B2:B11) formula generates row numbers from 2 to 11. The MOD(ROW(B2:B11), 2) = 0 part checks which of these rows are even. FILTER(B2:B11, MOD(ROW(B2:B11), 2) = 0) picks only the sales values in even-numbered rows. Finally, SUM adds up these filtered values.
This means the data adds the sales amounts for June 2nd, 4th, 6th, 8th, and 10th. These amounts are 150, 120, 210, 220, and 170.