Sum formula google sheets

Sum formula google sheets DEFAULT

Google Sheets offers plenty of tools to simplify complicated calculations, one of them being the SUM function. While it’s a basic formula, not every Google Sheets user knows about all the benefits of using it. Furthermore, the way you enter the formula and values may affect how the function works.

How To Sum a Row in Google Sheets

If you’re wondering how to sum an entire row in the app correctly, we’re here to help. In this guide, we’ll explain how to sum a row in Google Sheets the right way. We’ll also share instructions on how to add up only selected values or a range of cells. Additionally, we’ll answer some questions related to the topic.

SUM Functions in Google Sheets

The SUM function in Google Sheets, just as in Microsoft Office Excel, sums selected values. It’s helpful because it may be too much of a hassle to enter a formula if you only need to add up a few values. Sure, you can find “2+3+4” without the help of a computer. But in practice, the formula is extremely useful for any values.

The main benefit is that the sum updates automatically when any of the values are changed or added to a selected row or column. For instance, if you change “2” in the above-mentioned example to “1,” the value in the sum cell will update from “9” to “8” on its own.

On the other hand, without the formula, you’d have to recalculate the sum every time you make changes. For this reason, we advise you not to enter the value itself when using the “=SUM” formula. Instead, type in the number of a cell containing the value. We’ll take a closer look at how to use the function in the next section.

How to Enter the Formula

Now that you know why the SUM function is so useful, it’s time to find out how to use it. To sum an entire row, follow the instructions below:

  1. Click any blank cell.
  2. At the bottom of your screen, click “Enter text or formula” and type in “”.
  3. To sum a total row, click the number to the left from your row, for instance, “1.”
  4. Hit the “Enter” key or click the green checkmark to the left from your formula. The result will appear in your selected cell.

Note: When you select a whole row by clicking the number at the left, new values entered into this row are automatically added to the sum.

If you wish to only select certain cells, here’s the first way to add up only the selected values:

  1. Click any blank cell.
  2. At the bottom of your screen, click “Enter text or formula” and type in “”.
  3. To sum certain cells located in a random order, click on each one. You’ll see the cell numbers appear in your formula.
  4. To sum a range of cells – for instance, in the same row – type in the number of the first cell or click it.
  5. Type in the “” symbol without pressing space and enter the number of the last cell in your range or click it. Optionally, you can click and hold an edge of the frame around the first selected cell. Then, hold and drag it to select the range.

  6. Type in a closing parenthesis and hit the “Enter” key or click the green checkmark to the left from your formula. The result will appear in your selected cell.

Optionally, you can choose the required function from the menu instead of typing in the formula. Follow the steps below:

  1. Click any blank cell.
  2. Click the “fx” button. It’s located at the bottom left corner of your screen in the mobile version. On the desktop version, it’s located to the right from the formatting bar.
  3. Select “FUNCTION” from the menu, then “MATH” and select “SUM.”
  4. To sum certain cells located in a random order, click on each one. You’ll see the cell numbers appear in your formula.
  5. To sum a range of cells – for instance, in the same row – type in the number of the first cell or click it.
  6. Type in the “” symbol without pressing space and enter the number of the last cell in your range or click it. Optionally, you can click and hold an edge of the frame around the first selected cell. Then, hold and drag it to select the range.
  7. Hit the “Enter” key or click the green checkmark to the right from the formula bar. Your sum result will appear in the selected cell.

In this section, we’ll answer more questions about using the SUM function in Google Sheets.

To SUM It Up

Hopefully, our guide has helped you find the sum of values in a row in Google Sheets. When used smartly, this simple function can free you from unnecessary hassle. Follow our tips to select required values quicker and keep in mind the notes. Correct utilization of the SUM function eliminates the risk of a human mistake in calculations and ensures that the sum is updated accordingly to change in values.

Do you find the SUM function better implemented in Google Sheets or MS Excel? Share your opinions in the comments section below.


How to Sum a Column in Google Sheets (Easy Formula)

Adding some cells or a column is some of the most common things users do in Google Sheets.

If you have a column full of numbers, you can easily calculate the sum of the entire column (or a specific range in the column).

In this tutorial, I will show you how to use a simple formula to sum a column in Google Sheets.

Sum a Column using the SUM Function

Suppose you have the dataset as shown below and you want to get the sum of all the values in the column.

Column with values to Sum

Below is the formula that will give you the sum of all the values in the column:


SUM formula to add a range of cells

Enter this formula in cell A15 (or whichever cell where you want the sum of the column) and hit the enter key.

Google Sheets try to guess the range for you which you want to calculate the sum. When you enter the text =sum and then hit the tab key, Google Sheets will automatically select the range of cells that have the numbers.

Since this is a dynamic result, in case you change anything in any of the cells, the formula would automatically update.

While this works great, what if your data expands and you get new values that you need to be a part of the sum. While you can adjust the formula, there is a better way to make the formula dynamic.

Instead of just using the range that has the values, you include more cells that might get some additional data in the future.

In our example, I can use the below formula instead:


SUM function with extended range

Although cell A11 to A14 are empty as of now, these are still used in the formula. In case a value is added to these cells, the formula would automatically update to reflect these new values in the total sum.

Sum an Entire Column using the SUM Function

Another thing you can do when calculating the sum of all the values in a column is to include the entire column as a part of the formula.

For example, suppose you have the data as shown below and you want to add all the values in the entire column. At the same time, you want to make sure that in case any new value is added anywhere in the column, it’s also added to the total value.

Column with values to Sum

Below is the formula that will do this:


Sum an entire column

The above formula takes A:A as the input range, which represents the entire column.

Also, don’t worry about having any text value in the column, as text values are automatically ignored.

Note: For this to work, you need to enter the formula in a cell that is not in the same column. If you keep it in the same column, it would give a reference error as there would be a circular reference issue.

Remember that the SUM formula only adds those cells that have a numeric value. If there is any text, or of the number that has been formatted as text, it would be ignored.

So this is how you can use the SUM formula to get the total of a column in Google Sheets

I hope you found this tutorial useful!

Other Google Sheets tutorial you may like:

  1. Special request chit navy
  2. Classic theatre name crossword clue
  3. Franklin county board of disabilities
  4. Farmhouse laundry room cabinets
  5. Milwaukee rotary hammer drill 18v

The most primitive way to total cells in Google Sheets is to add the cell references to the formula and put plus () signs between them. For example:


For longer ranges of cells, such as an entire column or row, this solution is not handy at all. Instead, you should use the SUM function or one of its derivatives: SUMIF and SUMIFS. Let’s check out how the native functions work and which use cases are a fit for them. 

Google Sheets SUM to total a series of cells

SUM is a Google Sheets function to return a total of numbers or cells, or both specified numbers and cells. The SUM syntax has several variations depending on what you’re going to total.

SUM syntax to total values

Formula example 


SUM syntax to total cells

  • is the range of cells to total. The range can be specified using commas for scattered cells like or colon for integral cell ranges like . 

Formula example for scattered cells


Formula example for an integral cell range


SUM syntax to total values and cells

Formula example


Now you know the syntax of the SUM formulas, so let’s check out how they work on a dummy data set. To get one, we installed, a solution for importing data from multiple sources like Xero, Airtable, Pipedrive, and many more. Check out all the available integrations. As a dummy data set, we use the deals imported to Google Sheets from HubSpot. Here is how it looks:

How to SUM a column

SUM a limited range from a column

If you need to return total of a limited range from a column, like , a simple SUM syntax will do:


If you want to return total in the bottom of your column range, type the equal sign (), and Google Sheets will suggest you the SUM formula itself.

Note: This hack works only within 5 rows below the last value in a column.

SUM an entire column

If you need to total an entire column, specify the column range as follows:


Now, every new value within the column will be added to the total value, so you won’t have to manually tweak the SUM formula. 

How to SUM a row

For this use case, we took a Profit and Loss report imported from Xero. Now, let’s use SUM to total values from row 2.

Here is the SUM formula to total the row values:


How to return SUM of multiple rows in one column

The SUM formula above worked well, but is it possible to expand it to other rows using ARRAYFORMULA? Unfortunately, SUM + ARRAYFORMULA doesn’t expand: will give a single number. 

But there is a workaround.

Workaround#1: Sum multiple rows with ARRAYFORMULA

With this workaround, you don’t need the SUM function at all. The idea is to manually add the columns and use ARRAYFORMULA to expand the results. Here is the formula for our case:


Note: is needed to remove “0” for empty rows. 

Read our Guide of Using ARRAYFORMULA in Google Sheets.

Workaround#2: Sum multiple rows with ARRAYFORMULA, MMULT, TRANSPOSE, and COLUMN

For this workaround, we’ll need to nest four functions: ARRAYFORMULA, MMULT, TRANSPOSE, and COLUMN. Here is how the formula looks:


MMULT is an array function to multiply matrices. Our first matrix is . The second matrix must have the number of rows equal to the number of columns in the first matrix – five, in our case. This is how it should look:

Instead of manually tailoring the matrix we need, we’ll use the combination of two functions, TRANSPOSE and COLUMN, in the following way:

In the end, wrap up everything with ARRAYFORMULA.

The drawback of this workaround is that you have to specify the exact range of columns. 

How to return SUM of multiple columns in one row

Since we got the total column, let’s get the total row beneath the data set as well. MMULT nested with ARRAYFORMULA, TRANSPOSE and ROW will help with that. Here is how it looks:


Let’s wrap up with SUM for now, since we have more interesting cases with SUMIF and SUMIFS.

Google Sheets SUMIF to sum a data range on a condition 

SUMIF is a Google Sheets function to return a total of cells that match a single specific criterion. Put simply, the SUMIF functions filters the range according to the specified criteria and sums values based on this filter. The syntax is the same as SUMIF Excel.

SUMIF syntax

  • is a condition to filter data. can be a number, a text string, a cell reference, as well as a variety of conditions, such as greater than or equal to.
  • is a data range to examine based on the
  • is a data range with values to total. This parameter is optional and can be omitted in cases when is the range with values to total.

Now, let’s check out SUMIF formula examples with different criteria cases. For this, we’ll use a dummy data range that was already featured in the blog post COUNTIF vs. COUNTIFS. It’s an extract of a sandwich store’s database, in which you can see Product, Price, Quantity and other columns. By the way, this data set was imported from Airtable to Google Sheets.

SUMIF by a logical expression criterion: greater, less, or equal

Use one of the following logical operators to build a criterion for the SUMIF formula:

Logical expressionLogical operator
greater than>
less than <
equal to=
greater than or equal to>=
less than or equal to<=
except for<>

For example, let’s total the amount of products with the price equal to 10.


Another formula example is to total the amount of products that are greater than or equal to 8. Bring to notice that this formula syntax is free of , since the function will total the values from :


SUMIF not empty

The use of the logical operator “except for” () without any text string or cell reference means that the is “not empty cells“. For example, 


SUMIF by exact match 

The exact match criterion can be specified as a text string, numeric value or a cell reference. For example, here is the SUMIF formula to total the amount of a Sausage sandwich:

=sumif(E2:E,"sausage sandwich",H2:H)

SUMIF by a case-sensitive criterion

SUMIF does differentiate between upper- and lower-case letters. That’s why in the formula example above, SUMIF totalled the amount of Sausage sandwich, though we specified as the criterion. If case sensitivity matters, you can use the following workaround:

So, our formula will look like this and return “0” since there is no match to the criterion:

=sumif(arrayformula(find("sausage sandwich",E2:E)),1,H2:H)

How to expand SUMIF formula in a column: ARRAYFORMULA+SUMIF

Let’s expand the SUMIF formula to calculate the total amount of other products.

Here is the syntax you should use:

In our case, the SUMIF formula will look as follows:


Note: is needed to remove “0” for empty rows. 

How to sum and group values by a certain condition: QUERY+SUM

Here is an alternative solution to the SUMIF formula above. The idea is to extract unique values from the Product column and return the total amount of each product with a single formula. The QUERY function can easily do that as follows:

=query(E2:H21,"select E, sum(H) group by E")

Read our blog post for more about using QUERY in Google Sheets. 

SUMIF by partial match 

The partial match criterion is when you need to total cells in if the cells in contain specific characters. To tailor a partial match criterion, you’ll need to use the following wildcards:

  • Question mark () to disguise every single character of a text string. For example, means that the is a 6-letter word that ends with .
  • Asterisk () to disguise any number of characters of a text string. For example, means that that the is any word that contains .
  • You can also concatenate wildcards and cell references. For example, means that the is a text string that ends with the value from the C5 cell.

Let’s check out the common examples:

SUMIF formula to total all items that contain “cheese” in their naming:


And here is how this SUMIF formula will look if we use a cell reference concatenated with wildcards:


SUMIF by color

Google Sheets doesn’t have this feature built in. However, you can do it yourself using the Script editor. It won’t take more than a minute.

  • Open Script editor in your spreadsheet: go to Tools => Script editor
  • Open a new script file: go to File => New => Script file. Name the file as you want.
  • Copy and paste the following script to replace the original code.
function sumbycolor(sumRange,colorRef) { var activeRg = SpreadsheetApp.getActiveRange(); var activeSht = SpreadsheetApp.getActiveSheet(); var activeformula = activeRg.getFormula(); var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim(); var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds(); var sumValues = activeSht.getRange(countRangeAddress).getValues(); var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim(); var BackGround = activeSht.getRange(colorRefAddress).getBackground(); var totalValue = 0; for (var i = 0; i < backGrounds.length; i++) for (var k = 0; k < backGrounds[i].length; k++) if ( backGrounds[i][k] == BackGround ) if ((typeof sumValues[i][k]) == 'number') totalValue = totalValue + (sumValues[i][k]); return totalValue; };

Click Save (you might be offered to give a name to your project) and get back to the Google Sheets doc. 

Now, you have the SUMBYCOLOR function with the following syntax:

  • is a data range with values to total, as well as to examine based on the .
  • is a colored cell that defines the background color to filter and sum the data. 

Here is a formula example:


Kudos to ExtendOffice for the workaround. Check out their blog post if you also want to count cells by color.

Google Sheets SUMIFS to sum a data range based on multiple criteria 

SUMIFS is the youngest child in the SUM family. It lets you total values considering two or more criteria across different ranges. 

SUMIFS syntax

The parameters are the same as with SUMIF, but you can add multiple criteria to a single formula. 

SUMIFS works by the AND logic: to get the total sum, all the specified criteria must be met, otherwise the formula will return "0".

SUMIFS formula example

Let’s get back to our data set and total the amount of items by the following criteria:

  • Product name must contain “cheese
  • Product price must be less than $10

Here is the SUMIFS formula:


How to expand SUMIFS formula 

SUMIF can be nested with ARRAYFORMULA to expand the results. With SUMIFS, you can’t do that. However, if you badly need to expand SUMIFS, check out the workaround formulas for this.

SUMIF a data range based on multiple criteria with OR logic

SUMIFS returns a sum of values if all criteria are met based on the AND logic. The OR logic is when any of the specified criteria is met. This can be done with an advanced SUMIF formula.

SUMIF formula when any of the two criteria is met

This is the best solution when you need to SUMIF by different criteria for different . For example, we need to sum the values by one of the following criteria:

  • Product name must contain “cheese
  • Product price must be greater than $14

As you can see, none of the products in our data set has the price greater than $14. So, the SUMIF formula returns the sum of values that match the first criterion only:


SUMIF formula when any of multiple criteria is met

For multiple criteria within a single range, it’s not handy to use the approach above. So, you’d better use the following formula syntax:

Note: The formula works for a single only.

For example, we need to sum the values by one of the following product price criteria:

  • Equal to $13
  • Less than $9
  • Greater than $14

Here we go:

=sum(arrayformula(sumif(G2:G, {"=13","<9",">14"}, H2:H)))

To wrap up: How to learn the sum without any formulas

You’ve read the article till the end?! You’re a person of worth and deserve a tiny bonus. If you need to learn the total of specific cells right here, right now, you don’t need any formulas at all. Simply select the cells you want to sum and check out a new tab that appeared near the Explore button. Here is your total sum!

Quite handy, isn’t it? Good luck with your data!

Back to Blog
How to total a column in Google Sheets - SUM, SUMIF, SUMIFS formulas

How to Sum Columns or Rows in Google Sheets

What to Know

  • Easiest option: Click the cell, select SUM in the Functions menu, and select the cells you want to add.
  • Or click the cell, enter =SUM( and select the cells. Close with ). Press Enter.
  • You can also use the Function button to create a sum.

This article explains how to use the SUM function in Google Sheets using the Functions menu, inputting it manually, and with the Function button. The screenshots are from the Google Sheets app for iOS, but instructions are the same on all platforms.

How to Write a SUM Function

Adding up rows or columns of numbers is a common operation carried out in all spreadsheet programs. Google Sheets includes a built-in function called SUM for this purpose. With a function in place, the spreadsheet automatically updates when you make changes in the range of cells in the formula. If you change entries or add text to blank cells, the total updates to include the new data.

Using the information above, write a SUM function like this:

=SUM(number_1,number_2, ... number_30)

In this case, the numbers in the parentheses are the individual cells being added. This can be a list, like (A1, B2, C10), or a range, like (A1:B10). The range option is how you add columns and rows.

How to Enter a SUM Function in Google Sheets

Before you begin, enter the information you want to add up into a spreadsheet, then follow these steps:

  1. Click or tap the cell where you want to place the formula.

  2. Tap Enter text or formula to display the keyboard.

  3. Type =sum( to start the formula.

  4. Choose the numbers you want to add together. One way to do this is to tap the cells you want. The cell references appear inside the parentheses in the formula.

  5. To select a range of adjacent cells at once, tap one (for example, the first one in a row or column), then tap and drag the circle to select the numbers you want to add together.

    You can include empty cells in a function.

  6. Enter a closing parenthesis to end the function, and then tap the checkmark to run the function.

  7. The function runs, and the sum of the numbers you selected appears in the cell you chose.

  8. If you change any of the values in the cells you selected, the sum updates automatically.

How to Create a Sum Using the Function Button

You can also use a menu to enter a function instead of typing it. Here's how to do it.

  1. Enter the data, then select the cell in which you want the sum to appear.

  2. Click or tap the Function button.

    On the desktop version of Google Sheets, Function is on the right side of the formatting bar and looks like the Greek letter sigma (∑).

  3. In the list of function categories, tap Math.

    The Function menu on the desktop version of Google Sheets contains a few commonly used formulas. SUM may be on that list.

  4. The functions appear alphabetically. Scroll down, then tap SUM.

  5. In the spreadsheet, enter the range of numbers you want to add together.

How to Write a Function in Google Sheets

A function in Google Sheets and other spreadsheet programs like Microsoft Excel has three parts:

  • An equals sign (=). This tells the program that you're entering a function.
  • The name of the function. This is usually in all-caps, but that isn't necessary. Some examples are SUM, ROUNDUP, and PRODUCT.
  • A set of parentheses: (). If the function includes work on a set of numbers in the spreadsheet, these numbers go in the parentheses to tell the program which data to use in the formula.

Thanks for letting us know!


Google sum sheets formula

Device Links

Google Sheets is undoubtedly a part of the modern business starter pack. This useful app lets you keep your data organized, clear, and up-to-date at all times. And it’s quite user-friendly!

There’s plenty you can do with the data you enter into your columns and rows. Many available formulas can save you time and help you focus on the core of your tasks instead of wasting your time calculating things manually.

If your burning question right now is how to sum a column in Google Sheets, this article will give you all the answers.

What Is the Formula to Sum a Column in Google Sheets

Google Sheets uses pretty straightforward formulas to do any math operation you may need. Sometimes, you may even have more than one option.

The simplest formula to sum a column is the SUM function. This commonly used built-in function allows you to add any amount of numbers together quickly.

For example, you have a column of five numbers you want to sum, and they’re located in cells A1 to A5. The formula will look like this:

=SUM (A1:A5)

The = sign is an element you should always use with formulas in Google Sheets because it means you’re about to enter a function. After this, you should enter the name of the function, which in this case is SUM. It adds all the values from the range you will designate.

The parenthesis that follows the function name tells the program which cells need to be included in the formula. In this case, you will include the column letter and cell numbers in the parenthesis to mark the ones you want to add together.

To use this formula, you should type it in the cell where you want to see the results. When you’re done, press Enter and the sum will be displayed in the designated field.

Note that if you change the data in the cells you applied the formula to, the final result will also change to fit the new data.

To avoid adjusting the formula as you add new data, you can include empty cells, too. For example, if you only have data in cells A1-A5, and you want to sum the column as it is. But you know you’ll be adding more data in the future, so you can immediately set your formula like this:

=SUM (A1:A20)

This way, you won’t have to change the formula when you add new data later – the new values will automatically be added to the result.

If you’re not sure how many new cells you will fill with data, you can sum the entire column and not worry about adding new values. Each time you do it, it’ll be added to the total value without you needing to adjust the formula.

This is the formula to use:

=SUM (A:A)

Of course, you will type the appropriate letter, depending on the column you want to sum.

How to Sum a Column in Google Sheets on a Windows, Mac, or Chromebook PC

If you need to use Google Sheets on different devices, you’re in luck. The app is compatible with all types of PCs, mobiles, and tablets.

You may use different browsers to access Google Sheets, but the steps are the same no matter what kind of computer you’re using.

  1. Open the browser you typically use on your PC, Mac, or Chromebook.
  2. Open Google Sheets and open the desired file or click on Blank to open a new one.
  3. Click on a cell where you’d like to type in the formula.
  4. Enter the formula and the desired column you want to sum. It can be one column (A1:A20) or more (A1:C10).
  5. If you want to sum different columns, you can also click on the first cell you want to include and then drag the rectangle to the last one to select all the cells in between.
  6. When entering the formula, you will see the result in the desired cell, as soon as you type in the closing parenthesis and press Enter.

Another way to do this is to only enter the cell numbers you want to sum. To do so, you need to type in the data you want to add and then follow these steps:

  1. Navigate to the taskbar at the top.
  2. On the right, you’ll see the ∑ (the Greek letter sigma) sign. Click here and select the SUM formula from the drop-down menu.
  3. Enter the range of cells you’d like to add and press enter to sum the column.

How to Sum a Column in Google Sheets on the iPhone App

Google Sheets is also functional on mobile devices, which is great when you’re away from your computer, and you need to finish a task quickly.

If you’re an iOS user, here’s how to sum a column on the iPhone app:

  1. Open Google Sheets on your iPhone.
  2. Choose the desired spreadsheet or open a blank one.
  3. Enter the data or highlight the cells you want to sum.
  4. Navigate to the bottom, where you’ll see different options, such as sum, average, etc.
  5. Tap and hold on “sum” and drag the operation to the cell where you want the result to appear.
  6. Release to see the result.

How to Sum a Column in Google Sheets on an Android Device

Android users can also rely on their smartphones and tablets to calculate something on the go. If you need to use the SUM formula out of the office, or you simply don’t feel like turning on your computer to make a few clicks, here’s what to do:

  1. Launch the Google Sheets app on your Android device.
  2. Open a new spreadsheet or locate and launch the one you need to edit.
  3. Type in the necessary data or select the cells containing values you want to sum.
  4. At the bottom of your screen, there are different calculations to choose from: sum, minimum, maximum, and more.
  5. Tap on SUM, hold and drag the function to the desired cell.
  6. When you release the function, you will be able to see the result.

Take note that you can also enter the formula manually if, for any reason, you’re not able to drag it to the desired cell. This goes for both Android and iOS devices.

To do this, you need to tap the Enter text or formula field at the bottom and when your keyboard appears, just type in the formula. Select the cells you want to include by tapping on them, and they’ll be added to the formula automatically. When done, tap on the checkmark, and the result will display in the desired cell.

Being a Math Whiz with Google Sheets

Who knew math operations could be so simple?

With Google Sheets, you can effortlessly add large numbers in seconds, calculate averages, and do much more. Formulas enable you to sum different data and even entire columns. That way, you can stay on top of your tasks because you don’t have to waste your time doing all the work manually.

And the best thing about these functions is that the odds of you making a mistake are minimal.

Have you already tried the SUM function in Google Sheets? Share your experience in the comments section below.

How to Sum in Google Sheets - Without using formula - By using SUM formula

She appreciated the decoration of my apartment. - But it will go. - Thank you. Madam, if you are tired from the road and want to rest, then. - I said.

You will also be interested:

Firstly, the room was twilight. Secondly, from where I stood, I could not see it. But that was exactly how it was. Soon Lenka made a sound again, but now it was a completely different sound, not like a few minutes ago, now it didnt have that plaintive.

6266 6267 6268 6269 6270