Sum, Multiply, Subtract & Divide in Google Sheets

Published on

When it comes to summing, multiplying, subtracting, and dividing numbers in Google Sheets, you have multiple options. You can use either built-in functions or equivalent arithmetic operators. This post explains both methods, with a focus on functions.

Total a Column, Row, or Range

Use the SUM function to total a row, column, range, or a series of hardcoded numbers.

Syntax of the SUM Function in Google Sheets

SUM(value1, [value2, ...])
  • value1: The first number to add.
  • value2, …: Additional numbers to add to value1.

The argument value2, ... is optional, and you can specify column, row, or range references in value1 and value2, .... The size of the row, column, or range can vary. Also, note that the function ignores any text within the specified range.

Examples of Using the SUM Function in Google Sheets

=SUM(B2:B5)

Returns the total of the numbers in the range B2:B5. This is a closed range.

If you want to total an entire column starting from B5 downwards, use:

=SUM(B5:B)

Similarly, to sum values in a row range:

=SUM(A2:E2)

Returns the total of the numbers in A2:E2.

The following formula returns the total of all numbers in row #2.

=SUM(2:2)

Additional Examples

=SUM(10, 20, 30) // returns 60
=SUM(B2:C10) // returns the total of values in columns B and C

Add Two Numbers

To add two numbers, use the ADD function or the equivalent + arithmetic operator.

Syntax of the ADD Function in Google Sheets

ADD(value1, value2)
  • value1 – The first number.
  • value2 – The second number to add.

Examples of the ADD Function and the + Operator

=ADD(5, 10) // returns 15

Equivalent: =5+10

=ADD(A1, B1)

Equivalent: =A1+B1

If one or both values are text, the formula returns a #VALUE! error. If more than two arguments are provided, it returns a #N/A error.

For row-wise addition:

=ArrayFormula(ADD(B2:B10, C2:C10))
Total supply for two months calculated row by row in Google Sheets

The formula sums the total sales in January and February from B2:B10 and C2:C10, placing the results in D2:D10.

Equivalent formula:

=ArrayFormula(B2:B10+C2:C10)

Subtract Two Numbers

Use the MINUS function or the - arithmetic operator.

Syntax of the MINUS Function in Google Sheets

MINUS(value1, value2)
  • value1 – The first number.
  • value2 – The second number subtracted from value1.

Examples of the MINUS Function and the – Operator

=MINUS(10, 5) // returns 5

Equivalent: =10-5

=MINUS(A1, B1)

Equivalent: =A1-B1

The same error rules as the ADD function apply here (#VALUE! for text, #N/A for more than two arguments).

For row-wise subtraction:

=ArrayFormula(MINUS(B2:B10, C2:C10))
Example of subtracting advance from gross salary in Google Sheets

The formula returns the net salary by subtracting the advance in C2:C10 from the gross salary in B2:B10.

Equivalent formula:

=ArrayFormula(B2:B10-C2:C10)

Multiply Two Numbers

Use the MULTIPLY function or the * operator.

Syntax of the MULTIPLY Function in Google Sheets

MULTIPLY(factor1, factor2)
  • factor1 – The first value.
  • factor2 – The second value to multiply.

Examples of the MULTIPLY Function and the * Operator

=MULTIPLY(10, 5) // returns 50

Equivalent: =10*5

=MULTIPLY(A1, B1) // multiplies values in A1 and B1

Equivalent: =A1*B1

Errors:

  • #VALUE! if one or both values are text.
  • #N/A if more than two arguments are provided.

For row-wise multiplication:

=ArrayFormula(MULTIPLY(B2:B10, C2:C10))
Example of multiplying quantities by price in Google Sheets

In this example, the formula calculates the amount for each item in A2:A10 by multiplying the quantity in B2:B10 with the price in C2:C10.

Equivalent formula:

=ArrayFormula(B2:B10*C2:C10)

Divide Two Numbers

Use the DIVIDE function or the / operator.

Syntax of the DIVIDE Function in Google Sheets

DIVIDE(dividend, divisor)
  • dividend – The number to be divided.
  • divisor – The number to divide by.

Examples of the DIVIDE Function and the / Operator

=DIVIDE(100, 50) // returns 2

Equivalent: =100/50

=DIVIDE(A1, B1) // divides A1 by B1

Equivalent: =A1/B1

Errors:

  • #VALUE! if one or both numbers are text.
  • #DIV/0! if the divisor is 0.
  • #N/A if more than two arguments are provided.

For row-wise division:

=ArrayFormula(DIVIDE(B2:B10, C2:C10))
Sum quantities when given price and amount in Google Sheets

The formula returns the quantities by dividing the amount in B2:B10 by the price in C2:C10.

Equivalent formula:

=ArrayFormula(B2:B10/C2:C10)

Wrap-Up

We’ve covered how to sum, multiply, subtract, and divide in Google Sheets using both functions and arithmetic operators.

The ADD, MINUS, MULTIPLY, and DIVIDE functions take two arguments and return a single value. However, you can specify two one-dimensional ranges to get row-wise results.

When using arithmetic operators, you can specify more than two values or one-dimensional ranges. For example:

=ArrayFormula(B2:B10*C2:C10*D2:D10) or =B2*C2*D2

The SUM function allows multiple arguments and returns a single value.

These formulas make it easy to handle calculations efficiently in Google Sheets.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

6 COMMENTS

  1. I have figured out how to subtract one column from the previous column and dragged the formula all the way down. But now all the empty rows have a total of $0.00.

    Is there a way to get rid of that until I actually input some numbers to be subtracted?

    • Hi, Ellen,

      You can rewrite the formula =A2-B2 to =if(len(A2)+len(B2),A2-B2,)

      Further, there is no need to drag down the formula to cover more rows. Simply insert the below array formula in cell C2 (first empty column C and then insert the formula).

      =ArrayFormula(if(len(A2:A)+len(B2:B),A2:A-B2:B,))

  2. Is it possible to sum cells in multiple worksheets in Google Sheets the same way as in Excel? (I use =SUM(A:G!B12) to obtain the total of cell B12 in sheets from A TO G)

    Most grateful if you could provide an answer to this.

    Thanks

    • Hi, Richard Knight,

      As far as I know, the said feature is not available in Google Sheets.

      The following Excel formula …

      =SUM(sheet1:sheet2!B12)

      …must be replaced as below in Google Sheets.

      =SUM(sheet1!B12,sheet2!B12)

      Let’s hope Google will add this cool feature in Sheets in any of its future updates.

  3. How do I convert a column from ounces to pounds in Google Sheets? There are 5000 rows. So I sure don’t want to do each row!

    • Hi, Denise Goodwin,

      There is a specific function called CONVERT for unit conversion in Google Sheets.

      For example, assume the number to convert from ounces to pounds are in the range B2:B.

      Make column C empty and then insert the below Array Formula in cell C2 which will automatically expand down.

      =ArrayFormula(if(ISBLANK(B2:B),,convert(B2:B,"oz","pt")))

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.