Function Problems

How to Use an Excel Formula to Multiply Quantities and Calculate a Running Total

Algirdas Jasaitis Oct 8, 2026 869 views

Question details

The user needs a formula to multiply values in one column by corresponding values in another column and sum all the results, without knowing the exact final data row in advance.

How to Use an Excel Formula to Multiply Quantities and Calculate a Running Total

Product: Excel
Device & OS: not provided
Scenario: Calculating total material usage by multiplying item quantities and unit material amounts across an unknown or dynamic number of rows.
Observed behavior: Looking for a way to multiply corresponding values from column 5 and column 6 and sum them up continuously without using traditional programming loops.

Before you start

Ensure your data columns are clearly defined and do not contain mixed data types or text strings in the ranges you plan to multiply, as this can result in calculation errors.

Solution 1Recommended

Use the SUMPRODUCT Function for Dynamic Ranges

The SUMPRODUCT function is the most efficient way to multiply corresponding components in multiple arrays and return the sum of those products without relying on complex macros.

Excel calculates array products natively. SUMPRODUCT handles this seamlessly by multiplying ranges row by row and adding up the total. By utilizing R1C1 reference styles or large range references (like up to row 1048576), you can ensure the formula encompasses all future data entries.

Select the destination cell

Click on the specific cell where you want the final running total to be displayed.

Enter the SUMPRODUCT formula

Type the formula using your exact column references. For columns 5 (E) and 6 (F) starting from row 6, enter: =SUMPRODUCT(E6:E1048576, F6:F1048576). Alternatively, using R1C1 references, enter: =SUMPRODUCT(R6C5:R1048576C5, R6C6:R1048576C6).

Execute the calculation

Press Enter. The application will instantly multiply each quantity by its corresponding material amount and output the final grand total.

Use the SUMPRODUCT Function for Dynamic Ranges

Performance Tip: Referencing the entire column down to the absolute last row (1048576) can slow down workbook calculation speeds. For better performance, format your data as a defined Table or use a dynamic named range.

Advanced Spreadsheet Management

Calculate Running Totals Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas like SUMPRODUCT, allowing you to multiply and sum large datasets efficiently. It provides a familiar interface and is highly compatible with Microsoft Excel.

1. Open your data: Launch WPS Spreadsheet and open the workbook containing your quantities and material data.
2. Input the formula: Click on the cell designated for your total and input =SUMPRODUCT(E6:E1000, F6:F1000).
3. Get instant results: Press the Enter key to instantly calculate and view your total material usage.

100% compatible with Microsoft Excel formulas including SUMPRODUCTFast and optimized calculation engine for handling massive datasetsFree, lightweight, and easy-to-use alternative to Microsoft Office

microsoft office alternative - wps office

Frequently Asked Questions

Why is my SUMPRODUCT formula returning a #VALUE! error?

This error generally occurs if the referenced arrays do not share the exact same dimensions (e.g., E6:E100 and F6:F105) or if there is text within the selected numerical data range.

Can I use entire columns like E:E in SUMPRODUCT?

Yes, you can write =SUMPRODUCT(E:E, F:F), but doing so forces the spreadsheet application to calculate over a million rows, which can heavily impact system performance. Utilizing defined Tables is generally better practice.

Is there an alternative array formula I can use?

You can use an array formula with the standard SUM function, such as =SUM(E6:E100*F6:F100). Depending on your software version, you may need to press Ctrl+Shift+Enter to correctly apply it as an array formula.

Excel & Data Problems