How to Multiply in Google Sheets

Multiplication is one of the first things anyone needs from a spreadsheet, and Google Sheets gives you more ways to do it than most people realise. Some are one-off calculations, some scale to a hundred thousand rows without you dragging anything.

If you are still finding your feet, the basic Google Sheets formulas guide is a good companion to this one. This page walks through every multiplication method, from typing two numbers into a cell to multiplying two entire columns with a single formula, and finishes with the errors that actually come up.

Which multiplication method should you use?

The rule: if you are about to drag a formula down more than a screen's worth of rows, stop and use ARRAYFORMULA instead. Dragged formulas break the moment someone inserts a row at the bottom.

How do you multiply two numbers inside one cell?

Use this when you want a quick calculation without adding data to the sheet.

Step 1: Select the cell.

Click the cell where you want the result.

Step 2: Type the formula.

The pattern is an equals sign, the first number, an asterisk, then the second number. To multiply 9 and 2, type =9*2.

Step 3: Press Enter.

The cell shows 18. The formula stays in the formula bar, so you can change either number later without retyping the whole thing.

Read more: How to divide in Google Sheets.

How do you multiply two different cells?

This is the version you will use most. Referencing cells rather than typing numbers means the answer updates itself when the inputs change.

Step 1: Enter your numbers.

Type the values into two cells. For this example, put 5 in cell A1 and 4 in cell A2.

Step 2: Select the result cell.

Click the cell where the answer should appear. Here that is cell A3.

Step 3: Enter the formula.

Type =A1*A2 and press Enter. The result, 20, appears in A3. Change A1 to 6 and the result becomes 24 without you touching the formula.

How do you multiply more than two cells?

Chain the cell references with asterisks. There is no limit on how many you can string together.

Step 1: Enter numbers in cells.

Put 2 in cell A1, 3 in cell A2, and 4 in cell A3.

Step 2: Select the result cell.

Click the cell where the multiplication result should appear. In this example, cell A4.

Step 3: Enter the formula.

Type =A1*A2*A3 and press Enter. The result, 24, appears in A4.

The rule: once you are chaining more than three or four cells, switch to PRODUCT. A long chain of asterisks is easy to break and impossible to read six months later.

How do you use PRODUCT and MULTIPLY?

Google Sheets has two named multiplication functions and they are not interchangeable.

• PRODUCT takes a range or a list and multiplies everything in it. =PRODUCT(A1:A5).
• MULTIPLY takes exactly two arguments and nothing more. =MULTIPLY(A1, B1) is the function form of =A1*B1. Hand it a range and it returns an error.

There is one behaviour worth knowing. PRODUCT skips blank cells and text rather than treating them as zero, so a gap in the middle of your range will not wipe the answer out. Plain asterisk multiplication does treat a blank as zero, and the whole result collapses to 0.

Step 1: Enter your numbers.

Type your values into cells A1 through A5.

Step 2: Select the result cell.

Click the cell where the result should appear, for example cell A6.

Step 3: Enter the PRODUCT formula.

Type =PRODUCT(A1:A5) and press Enter. Sheets multiplies every number from A1 to A5 and shows the result in A6.

You can also mix ranges and single values: =PRODUCT(A1:A5, 2) multiplies the range and then doubles it.

How do you multiply a whole column by one number?

The trick is the dollar sign. It pins a reference so it does not shift when you fill the formula down.

Step 1: Enter your numbers.

Type your values into cells B1 through B5.

Step 2: Put the multiplier in its own cell.

Type the constant into a cell of its own, for example 2 in cell C1. Keeping it in a cell rather than typing it into the formula means you can change the rate later in one place.

Step 3: Multiply each cell by the constant.

Click cell D1, type =B1*$C$1 and press Enter. Then drag the fill handle, the small square at the bottom right of D1, down to D5. Every row multiplies its own B value by the single value in C1.

Press F4 while the cursor is on a reference to cycle it through the absolute and relative forms rather than typing the dollar signs by hand.

The rule: without the dollar signs, dragging =B1*C1 down turns row 2 into =B2*C2, and C2 is empty, so the answer becomes 0. That is the single most common multiplication mistake in Google Sheets.

If your multiplier is a percentage, remember that a cell showing 10% holds the value 0.1. Multiply by the cell, not by 10. Our Google Sheets percentage formula guide covers that in full.

How do you multiply two columns together?

Two columns, row by row, is the most common real spreadsheet job: price in one column, quantity in another, line total in a third. You have three options.

Option 1: fill down (fine for a fixed list)

In C2 type =A2*B2, then double click the fill handle to copy it to the bottom of the existing data. Simple, but it stops at whatever row your data currently ends on. New rows get nothing.

Option 2: ARRAYFORMULA (use this one)

Put a single formula in C2 and leave the rest of the column empty:

=ARRAYFORMULA(A2:A*B2:B)

That multiplies every row at once and keeps working as rows are added. There is one wrinkle. Because the range is open ended, the empty rows below your data return 0 and fill the column with zeroes. Wrap it in an IF to suppress them:

=ARRAYFORMULA(IF(A2:A="", "", A2:A*B2:B))

Option 3: SUMPRODUCT (when you only want the total)

If you do not need the line totals at all, only the grand total, skip the helper column entirely:

=SUMPRODUCT(A2:A, B2:B)

SUMPRODUCT multiplies the two columns row by row and then adds every result together. One cell, no helper column, no dragging.

The rule: ARRAYFORMULA when you need each line total on screen, SUMPRODUCT when you only need the number at the bottom.

Can you use Paste special to multiply in Google Sheets?

No. This is worth stating plainly because a lot of guides imply otherwise. Excel's Paste Special dialog has an Operation section with Add, Subtract, Multiply and Divide, which lets you multiply a range in place without a helper column. Google Sheets has no equivalent. Its Paste special menu offers Values only, Format only, Formula only, Transposed and similar, and none of them perform arithmetic.

Here is the workaround that gets you the same result, multiplying a column in place and leaving no extra columns behind.

1. Put your multiplier in a spare cell, say 1.1 in cell F1.
2. In the first free column, enter =B2*$F$1 and fill it down alongside your data.
3. Select that helper column and copy it with Ctrl + C.
4. Select the original column, then use Edit > Paste special > Values only, or press Ctrl + Shift + V.
5. Now delete the helper column and the multiplier cell.

Order matters. Paste the values before you delete the helper column. The helper formulas point at the original column, so once you overwrite it they will recalculate to nonsense. Pasting first captures the correct numbers, and deleting afterwards is then harmless.

How do you multiply only the rows that meet a condition?

Two different questions hide behind this, and they need different formulas. Be clear which one you are asking.

Case 1: multiply two columns, but total only the matching rows

This is what SUMPRODUCT is for, and it is what most people actually want. Say column A holds price, column B holds quantity and column C holds order status:

=SUMPRODUCT((C2:C="Paid")*A2:A*B2:B)

The bracketed condition evaluates to TRUE or FALSE on every row, which Sheets reads as 1 or 0. Rows that fail the test get multiplied by zero and drop out. Rows that pass contribute price times quantity. SUMPRODUCT then adds up what is left.

The same pattern with numeric bounds looks like this:

=SUMPRODUCT((B1:B5>=10)*(B1:B5<=40)*B1:B5)

Read that carefully, because it is widely misdescribed. This returns the sum of the values in B1 to B5 that fall between 10 and 40. It does not multiply those values together. SUMPRODUCT always ends in a sum, which is exactly what the name says.

Case 2: genuinely multiply the qualifying values together

If you really do want the product of only the cells that pass a test, you need PRODUCT with an IF inside an array, and a 1 as the fallback so that non qualifying rows leave the answer alone:

=ARRAYFORMULA(PRODUCT(IF((B1:B5>=10)*(B1:B5<=40), B1:B5, 1)))

Every row that fails the test contributes a 1, and multiplying by 1 changes nothing. Every row that passes contributes its own value.

The rule: if the answer should be a total, use SUMPRODUCT. If the answer should be a product, use PRODUCT with an IF and a fallback of 1. Using zero as the fallback wipes the whole result out.

Why is your multiplication formula not working?

What if you are working in Excel instead?

The asterisk operator, PRODUCT and SUMPRODUCT all behave the same way in Excel. The differences are that Excel has no MULTIPLY function, it does have Paste Special with a Multiply operation, and it does not need ARRAYFORMULA because modern Excel spills array results on its own. If your file is an Excel workbook, start with how to multiply in Excel.

Conclusion

Multiplication in Google Sheets is one asterisk and a handful of decisions. Use a cell reference rather than a typed number so the sheet updates itself. Lock the multiplier with dollar signs the moment you start filling down. Reach for ARRAYFORMULA before you reach for the fill handle on a long column. Those three habits prevent most of the errors in the table above.

Visit Simple Sheets for more easy-to-follow guides and examples, and take a look at the related articles below.

Subscribe to Simple Sheets on YouTube for the most straightforward Excel video tutorials.

Frequently asked questions

What is the multiply formula in Google Sheets?

It is the asterisk. Type an equals sign, the first value, an asterisk, then the second value, for example =A1*B1. There is also a named function, =MULTIPLY(A1, B1), which does exactly the same thing but accepts only two arguments.

How do I multiply two entire columns without dragging the formula down?

Put =ARRAYFORMULA(IF(A2:A="", "", A2:A*B2:B)) in the first cell of the result column and leave the rest of that column empty. It calculates every row at once and keeps working as new rows are added.

Is there a Paste special multiply option in Google Sheets?

No. Excel has one, Google Sheets does not. Use a helper column with =B2*$F$1, copy it, then paste back over the original column with Edit > Paste special > Values only, and delete the helper column afterwards.

Why am I getting a #VALUE! error when I multiply?

One of the cells contains text rather than a number, usually because a unit or a currency symbol was typed in by hand. Remove the characters so the cell holds only the number, then apply currency or unit formatting instead of typing it.

What happens if I multiply a number by a blank cell?

You get 0, because Google Sheets treats an empty cell as zero in a multiplication. Use =PRODUCT() instead if you want blanks to be skipped rather than to zero out the answer.

Can I multiply numbers across different tabs?

Yes. Put the tab name and an exclamation mark in front of the cell reference, for example =A1*Sheet2!B1. If the tab name has a space in it, wrap it in single quotes: ='Raw Data'!A1*B1.

Related Articles

How to Compare Two Columns of Excel

How to Change Currency in Google Sheets

How to Add Leading Zeros in Microsoft Excel

How to Sum a Column in Google Sheets