How to Calculate Total Products of Hours and Rates in WPS Spreadsheets
Question details
User needs to calculate a single total from rows where hours worked are in column C and variable hourly rates are in column D by multiplying and adding each corresponding pair.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A spreadsheet contains time entries with hours in column C and variable hourly rates in column D, and the user wants a combined total.
- Observed behavior
- The desired result is a single value equal to the sum of each row’s hours multiplied by its corresponding hourly rate.
Make sure the hours are in column C, the hourly rates are in column D, and both ranges start and end on the same row. Check that rate cells use a numeric or currency format so the multiplication result is accurate.
Use SUMPRODUCT to multiply and total hours and rates
Best for a compact, formula-only total that does not require helper columns.
SUMPRODUCT multiplies each corresponding value in two ranges and then adds the products. It is the most direct way to calculate total pay or total cost when each row has its own hourly rate.
Click an empty cell where you want the total to appear, such as E2.
Type =SUMPRODUCT($C$2:$C$15,$D$2:$D$15), and adjust $2:$15 to match the first and last data row in your sheet.
Press Enter. WPS Spreadsheets calculates the sum of each hour value multiplied by its corresponding rate.
Create a row-by-row product column and use SUM
Use this if you also need to inspect each row’s product before totaling.
Calculate total hours by variable rates in WPS Spreadsheets with SUMPRODUCT
Open your workbook in WPS Spreadsheets, enter the same SUMPRODUCT formula used in Excel, and the application returns the combined total of each row’s hours multiplied by its rate.
- 1. Open the workbook: Launch WPS Office and open the .xlsx or .xls workbook that contains your hours and rates.
- 2. Select the target cell: Click the empty cell where you want the total products result to appear.
- 3. Enter SUMPRODUCT: Type =SUMPRODUCT($C$2:$C$15,$D$2:$D$15), replacing the row numbers with your actual data range.
- 4. Press Enter to calculate: Press Enter, and WPS Spreadsheets immediately calculates the total value of all hours multiplied by their corresponding rates.
Frequently Asked Questions
What does the SUMPRODUCT formula do when the hours and rates ranges are different sizes?
The ranges must be the same size; otherwise WPS Spreadsheets returns a #VALUE! error. Make both arguments cover the same number of rows, such as C2:C15 and D2:D15.
Can SUMPRODUCT ignore blank cells or text in the hours and rates columns?
SUMPRODUCT treats non-numeric entries as zero in most cases, but to avoid unexpected results, keep the ranges numeric or use IF and VALUE to convert text values before multiplying.
How do I change the formula to total only rows that meet a condition?
Use a conditional SUMPRODUCT such as =SUMPRODUCT((A2:A15="Project A")*(C2:C15)*(D2:D15)), replacing the condition and ranges with your own.
Why does my result show 0 even though my hours and rates look correct?
Your rate cells may be formatted as text, so SUMPRODUCT evaluates them as zero. Select the rate cells, press Ctrl+1, choose Number or Currency, and re-enter the values or apply Text to Columns to convert them.
