Are you tired of manually calculating the sum or difference between two or more cells in Google Sheets? Looking for a quick and efficient way to get the total or the difference? Look no further! In this article, we’ll explore how to use the SUM and MINUS functions to add and subtract columns in Google Sheets.
The SUM and MINUS Formulas
Let’s start by understanding the syntax of the SUM and MINUS functions. The SUM function allows you to add multiple numbers, cells, or ranges together. The syntax is as follows:
=SOMME(value1, [value2, ...])value1: The first number, cell, or range to be added.value2: (Optional) The second number, cell, or range to be added.
On the other hand, the MINUS function calculates the difference between two numbers, cells, or ranges. The syntax is as follows:
=MINUS(value1, value2)value1: The number, cell, or range to be subtracted from.value2: The number, cell, or range to subtract.
How to Use the SUM Function in Google Sheets
When you want to get the total of data in Google Sheets, you can add cells or an entire column using the SUM function. Here’s how:
1. Choose an empty cell to display the sum.
Start by selecting an empty cell where you want to show the sum. For example, you can choose a cell at the end of the “Box Office Revenue” column or next to the data you want to add.
2. Use the SUM function to add two cells.
Begin typing =SUM in an empty cell, and Google Sheets will automatically display the SUM function =SUM(value1, value2). The comma tells Sheets to add these values. The values can be specific cells, numbers, or ranges.
To add two cells, your values will be the cells you want to add. For example, =SUM(A2, A3) will add cells A2 and A3. Or =SUM(A2, A3, A4) will add cells A2, A3, and A4.
3. Use the SUM function to add a range of cells.
If you want to get the total of a range of cells (which also works for an entire column), you only need to specify a single value in your SUM function, and that value will be a range =SUM(value1). A range is written as two cells separated by a colon [first cell]:[last cell].
For example, to add all the movies from the 1980s (cells F35 to F44), our formula would be =SUM(F35:F44).
4. Use the SUM function to add a specific number.
If you want to add a specific number to the data you have accumulated, you can use a number as one of your values.
For example, if you want to know how much additional ticket sales $1,350 represents for the movie “Batman” in 1989, the function would be =SUM(F35, 1350).
How to Subtract in Google Sheets
Similar to the SUM function, you can use the MINUS function to calculate the difference between two cells or an entire column.
1. Choose an empty cell to display the difference.
Just like with SUM, choose any empty cell that makes sense, next to a row of numbers or at the end of a column of numbers.
2. Use the MINUS function to subtract cells.
Start by typing =MINUS in an empty cell, and Google Sheets will automatically populate the MINUS function =MINUS(value1, value2). The comma tells Sheets to subtract these values. Note that the MINUS function can only handle two values, and your values can be specific cells, numbers, or a range.
To subtract two cells, your values will be the two cells. For example, let’s say we want to find out how much more “Black Panther” made compared to “Avengers: Endgame”. Our function becomes =MINUS(F6, F5) as we know that “Black Panther” (F6) made more, and we want to subtract “Avengers” (F5).
If you subtract F5 from F6, you will get the same number, but it should have a negative sign since “Avengers” didn’t earn as much.
3. Subtract multiple cells from the total of a cell.
If you want to subtract multiple cells from the total of a cell, you’ll need to use the subtraction formula and the SUM function. (The MINUS function won’t work here since it’s designed to compare a maximum of two values.)
The formula to subtract multiple cells from a cell is =value1-SUM(value2, value3). This tells Sheets to add your second and third values together and subtract them from the first.
For example, let’s add “Bad Boys for Life” and “Spider-Man: No Way Home” and subtract them from the total of “Top Gun: Maverick”. Our formula becomes =F2-SUM(F3:F4).
How to Copy the SUM and MINUS Formulas to an Entire Column
Sometimes, you may need to apply the SUM or MINUS functions to an entire column, capturing the total or difference of two or more cells for each row. It’s possible to easily copy the SUM function and apply it to an entire column.
1. Add the function to your first cell.
Let’s say you want to add columns A and B to understand the relationship for each row. Start by adding the SUM function to your first cells, A2 and B2. (The steps are the same for the MINUS function as well.)
2. Double-click the small blue square.
You’ll notice a small blue square appearing in the bottom-right corner of your selected cell. Double-click on it to apply the function to the entire column.
Common Errors in SUM and MINUS Functions and Solutions
The following issues often occur when using SUM or MINUS functions. If you’re not getting the desired results, follow these tips:
- Error #N/A: The MINUS function can only handle two values, so if you try to add more, such as subtracting a range, you’ll see #N/A since Sheets expected two values (or “two arguments,” as it will explain) and got more than that.
- If the function doesn’t return any result or you get “0” as the total: There might be text somewhere in your cells. For example, Sheets considers currency symbols as text, so adding cells or columns indicating currency ($25 or £100) can create an error. Double-check that you’re only adding or subtracting numeric values.
- Clear a function before using another one: If you want to apply a function to an entire column that already contains an existing function, you’ll need to clear those data first before Sheets can apply the new function.
Ready to become a Google Sheets pro? Explore more about common data analysis tools like spreadsheets by signing up for the Google Data Analytics Professional Certificate. Learn at your own pace, which means you can learn when it’s convenient for you, and it takes approximately six months to complete.
Explore More
Learn more about key data analysis tools like spreadsheets by signing up for the Google Data Analytics Professional Certificate. You can learn at your own pace, and it takes approximately six months to complete.
Now that you’re equipped with the knowledge of adding and subtracting columns in Google Sheets, go ahead and make your data calculations a breeze!
