Excel Formula to Multiply Job Quantities by Fixed Column Rates
Question details
Calculate daily job amounts by multiplying each job quantity by the fixed rate assigned to its column.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Job quantities are entered by day in columns B through E, and each job column has its own fixed rate.
- Observed behavior
- Each calculated amount should use the matching job quantity and column rate, while the rate row remains unchanged when formulas are copied down.
Identify the cells containing job quantities and fixed rates, and make sure both contain numeric values. Use separate cells for calculated amounts so entering a formula does not replace a job quantity.
Multiply each job quantity by its fixed column rate
Use a mixed reference to keep the rate row fixed while allowing the job column to change when the formula is copied across.
The example below uses B1:E1 for fixed rates and B2:E2 for one day's job quantities. In B$1, the dollar sign locks row 1 but leaves column B relative; $B$1 would instead lock both the column and row.
Enter each job's fixed rate in B1, C1, D1, and E1. Enter the corresponding daily job quantities in B2:E2 and subsequent rows.
Reserve columns G:J for calculated amounts, matching job columns B:E. In G2, enter =B2*B$1 and press Enter.
Select G2 and drag its fill handle across to J2. The formulas will become =C2*C$1, =D2*D$1, and =E2*E$1 in the adjacent result cells.
Select G2:J2 and drag the fill handle down through the remaining daily rows. The quantity row changes for each day, while every formula continues to use the rates in row 1.
Calculate one daily total across all job columns
Use SUMPRODUCT when you need a combined daily amount rather than separate amounts for each job type.
Calculate Daily Job Amounts in WPS Spreadsheets
WPS Spreadsheets supports multiplication formulas, mixed references, and SUMPRODUCT, so you can calculate job amounts with fixed column rates and reuse the formulas for each day.
- 1. Open your worksheet: Launch WPS Spreadsheets and use Open to select your Excel workbook, or create a blank spreadsheet.
- 2. Set up rates and quantities: Enter fixed job rates in B1:E1 and the first day's quantities in B2:E2. Reserve a separate area for calculated amounts.
- 3. Calculate the job amounts: Select G2, type =B2*B$1, and press Enter. Drag the fill handle across to J2, then select G2:J2 and fill down for additional days.
- 4. Add a combined daily total: Select F2 and enter =SUMPRODUCT(B2:E2,$B$1:$E$1). Fill this formula down to calculate the total amount for each day.
Frequently Asked Questions
Why use B$1 instead of $B$1?
B$1 locks only the rate row. When you copy =B2*B$1 across, the rate reference changes to C$1, D$1, and so on. $B$1 would make every copied formula use the rate in B1.
Can I enter a quantity and its multiplication formula in the same cell?
Not with a standard worksheet formula. Typing a quantity would overwrite the formula, and a formula that refers to its own cell would create a circular reference. Keep quantities in B:E and place calculated amounts in a separate area, such as G:J.
What if my fixed rates are below the daily job quantities?
Reference the actual rate row and lock its row number. For example, if the rate is in B10 and the quantity is in B2, enter =B2*B$10 in a separate result cell. When filling down, stop before any rows that contain rates or summaries.
How do I get one total for all jobs completed in a day?
If quantities are in B2:E2 and rates are in B1:E1, enter =SUMPRODUCT(B2:E2,$B$1:$E$1) in F2. It multiplies each quantity by its matching rate and adds the results.
Will these formulas work in WPS Spreadsheets?
Yes. Open the workbook in WPS Spreadsheets and enter =B2*B$1 for a single job amount or =SUMPRODUCT(B2:E2,$B$1:$E$1) for a daily total. You can copy these formulas with the fill handle and save the workbook in Excel format.
