Free tool

Multiply columns and add them up

The most common spreadsheet calculation: quantity times price on every row, then a total. Change any quantity or price and every line and total follows.

fx=SUM(D2:D6)
ABCD
1ItemQuantityPriceLine total
2Notebook13.50
3Pens12.00
4Stapler12.99
5Folders13.50
6Tape12.40
7Total2464.39
8In one formula64.39
9Average price2.68

Blue cells are inputs: type a new value and the sheet recomputes. Click a result to see its formula.

The formulas

How it works, step by step

=B2*C2
Multiply two cells. The asterisk multiplies. Type it in the first row, then drag the fill handle down (or double-click it): the references move with each row, so D3 becomes =B3*C3.
=SUM(D2:D6)
Sum a column. SUM adds every number in the range and skips text and empty cells. Select the cell under a column and use AutoSum to have the range filled in for you.
=SUMPRODUCT(B2:B6,C2:C6)
The total in one formula. SUMPRODUCT multiplies the two columns row by row and adds the results, so you get the grand total without a line-total column.
=C2*$F$1
Multiply a whole column by one number. To apply one rate (tax, a discount, an exchange rate) to every row, put it in one cell and lock the reference with $ signs, so it stays fixed when you fill down.
On live data

The same formula on your Drop Top Sheets data

In Sheets the line totals of a store come in live, already multiplied: one formula adds them up, and a filter column narrows it, e.g. =data.order_lines.sum(total, country, "Belgium").

Sheets for Drop Top Sheets

=data.order_lines.sum(total)
Sheets reads the table live from the connected account: no export, no copy-paste, and the result updates when the data does.

Functions used here

SUMSum of valuesSUMPRODUCTSum of element-wise productsPRODUCTProduct of valuesSUMIFSum where one criterion matches

More free tools

Percentage calculatorWork out a percent of a number, what percent one number is of another, and the percent change between two values, with the spreadsheet formula for each.Credit card interest calculatorSee the interest on a credit card balance this month, how many months your payment takes to clear it, and the total interest, with the formulas behind each number.Cubic feet calculatorCalculate cubic feet from length, width and height in feet or inches, convert to cubic yards and cubic meters, and get the volume of a cylinder, with the formulas.Box and whisker plot makerPaste your numbers to draw a box and whisker plot with the minimum, quartiles, median, maximum, IQR and outliers, each worked out by a spreadsheet formula you can copy.Find duplicates in a listPaste a list to flag every duplicate, count the distinct values and list the ones that repeat, with the COUNTIF and UNIQUE formulas that do it in any spreadsheet.Profit margin calculatorCalculate gross profit margin and markup from cost and price, or the selling price for a target margin or markup, with the spreadsheet formula for each.CAGR calculatorWork out the compound annual growth rate between a start and end value over any number of years, check it with RRI, and project future values with the same rate.
FAQ

Questions

How do I multiply two columns in a spreadsheet?

In the first row type =B2*C2, then fill it down the column. Each row multiplies its own two cells.

How do I add up a column?

Use =SUM(D2:D6) with the range of the column, or =SUM(D:D) for the whole column when nothing else is in it.

Is there a single formula that multiplies and adds?

Yes, SUMPRODUCT: =SUMPRODUCT(B2:B6,C2:C6) gives the sum of quantity × price over all rows, 64.39 in the example.

How do I multiply a column by a percentage?

Put the percentage in a cell, for example 21% in F1, and use =C2*$F$1 for the amount or =C2*(1+$F$1) for the price including it. The $ signs keep F1 fixed while you fill down.

Why does my total show 0?

The numbers are probably stored as text, for example after pasting from another system. Text is left-aligned; convert it with =VALUE(C2) or by multiplying by 1.

Run these formulas on live data

Sheets is a spreadsheet with 491 functions that reads your store, accounting or project data live, builds dashboards and e-mails reports on a schedule.

See the integrationsAll free tools