How to Calculate Quantity Totals and Price Amounts Automatically in Excel
Question details
The user needs to calculate line amounts from quantity and price columns in a spreadsheet and then calculate quantity counts and totals automatically without manual arithmetic.
- Product
- WPS Office Spreadsheet (Excel-compatible)
- Device & OS
- not provided
- Scenario
- The user has a sheet containing quantity and price columns and wants the amount, count, and grand total to update automatically from the entered data.
- Observed behavior
- The user currently does not have an automatic formula setup for line amounts, counts, and totals, so those values would require manual calculation or manual formula adjustment.
Open the WPS Office spreadsheet and confirm which columns contain quantity, price, and where the amount column should appear. If the list will grow, select the data range and convert it to an Excel Table before entering formulas.
Use an Excel Table for Automatic Quantity and Price Calculations
Best for dynamic or growing lists because structured references and a total row update automatically when rows are added.
An Excel Table lets you reference columns by name, such as =[@Quantity]*[@Price], so you do not need to adjust cell ranges when data expands.
Select your quantity, price, and amount columns, go to Insert > Table, confirm the range, check My table has headers, and click OK.
In the first amount cell, type =[@Quantity]*[@Price] and press Enter. WPS Office fills the formula down the entire amount column.
Go to Table Tools > Total Row and turn it on. In the Amount column total cell, choose Sum from the dropdown to calculate the grand total.
In the Quantity column total cell, choose Count from the dropdown to count numeric quantity entries automatically.
Use Standard Formulas to Calculate Line Amounts, Counts, and Totals
Use this for fixed ranges when you prefer to enter formulas manually and do not need automatic table expansion.
Calculate Quantity and Price Totals Automatically in WPS Office Spreadsheet
Use WPS Office Spreadsheet to multiply quantity by price, count entries, and sum line amounts with standard Excel-compatible formulas or Excel Table structured references. It is free, lightweight, and saves .xlsx files that remain fully compatible with Microsoft Excel.
- 1. Install WPS Office: Download the free WPS Office installer from the official website, install it, and open WPS Office Spreadsheet.
- 2. Open your workbook: Open the .xlsx file that contains quantity and price columns, or create a new spreadsheet with Quantity, Price, and Amount headers.
- 3. Insert a table or enter formulas: Select the data range and go to Insert > Table, or enter =A2*B2 in the first amount cell for a standard range.
- 4. Apply multiplication and totals: In a table, enter =[@Quantity]*[@Price] and enable the total row with Sum. For a standard range, use COUNT and SUM formulas.
- 5. Save and share: Save the completed workbook as .xlsx to preserve formulas and share it with Microsoft Excel users without compatibility issues.
Frequently Asked Questions
What formula do I use to multiply quantity by price in WPS Office Excel?
If Quantity is column A and Price is column B, use =A2*B2 in the Amount cell. In an Excel Table, use =[@Quantity]*[@Price], and WPS Office fills the formula down automatically.
How can I total the amount column after calculating line amounts?
Use =SUM(C2:C4) for a fixed range. If the data is an Excel Table, turn on Table Tools > Total Row and choose Sum in the Amount column dropdown.
How do I count the number of quantity entries?
Use =COUNT(A2:A4) to count numeric quantity cells. In a table total row, select Count under the Quantity column to count entries automatically.
Will the formulas update when I add new rows?
If you convert the data to an Excel Table, the structured formula and total row update automatically. In a standard range, extend the formula and SUM/COUNT ranges manually.
Can I open the WPS Office spreadsheet in Microsoft Excel later?
Yes. WPS Office saves standard .xlsx files, and multiplication, COUNT, and SUM formulas open and recalculate normally in Microsoft Excel.
