Function Problems

How to Automatically Sum Invoice Quantities Between Realized Values in WPS Spreadsheets

Tauseeq MagsiTauseeq Magsi Nov 13, 2026 869 views

Question details

The user needs an Excel-compatible formula in WPS Spreadsheets to automatically sum invoice quantities from each realized value to the next nonblank realized value instead of manually summing more than 1,000 rows.

How to Automatically Sum Invoice Quantities Between Realized Values in WPS Spreadsheets
Product
WPS Office Spreadsheets
Device & OS
not provided
Scenario
The user has an invoice worksheet with quantity and realized-value columns and needs subtotals for invoice quantities between nonblank realized values.
Observed behavior
Goal state: each row next to a realized value should display the sum of quantities since the previous realized value after filling the formula down.
Before you start

Open the workbook in WPS Spreadsheets and check that your invoice data is arranged with quantities in one column and realized values in another. Make sure row 2 is the first data row so the fill-down formula has a consistent starting point.

Solution 1Recommended

Use an INDEX, LARGE, IF, MATCH, and SUM formula to total invoice quantities between nonblank realized values

Use this fill-down formula when you need automatic subtotals between each realized value without manually summing rows.

The formula finds the previous nonblank realized value with LARGE, IF, and MATCH, then calculates the SUM from that row to the current row. Adjust the column references if your quantity or realized-value columns differ.

1
Open the worksheet and verify your columns

Open your invoice workbook in WPS Spreadsheets and confirm Column A contains invoice quantities, Column C contains realized values, and data starts at row 2. Adjust the formula references if your worksheet uses different columns.

2
Enter the formula in cell B3

Click cell B3, then type or paste =IFERROR(SUM(INDEX($A$2:A3,LARGE(IF($C$2:C3<>"",ROW($C$2:C3)),2)):INDEX($A$2:A3,MATCH(C3,$C$2:C3,0))),"") and press Enter. If your version does not support dynamic arrays, press Ctrl+Shift+Enter to confirm it as an array formula.

3
Fill the formula down through all invoice rows

Select B3, drag the fill handle at the lower-right corner downward through the last invoice row to apply the formula.

4
Add a separate formula for the first data row if needed

If B2 is the first data row and returns an error or blank because no previous realized value exists, enter a simple reference such as =IF(C2<>"",A2,"") in B2 or adjust the formula range to start from the first header-adjacent row.

First data row may require separate handling: The main fill-down formula assumes at least one previous realized value exists, so the first data row may need its own formula.
WPS Spreadsheets

Calculate Invoice Subtotals Automatically in WPS Spreadsheets

Use WPS Spreadsheets to enter a combined INDEX, LARGE, IF, MATCH, and SUM formula so invoice quantities between realized values are totaled automatically, without manually summing more than 1,000 rows.

  1. 1. Open the invoice workbook: Launch WPS Office, click Spreadsheets, and open the invoice workbook you want to calculate.
  2. 2. Confirm the data layout: Check that Column A is invoice quantity, Column C is realized value, and data starts at row 2. Adjust references if your columns differ.
  3. 3. Enter and fill the formula: Click B3, enter =IFERROR(SUM(INDEX($A$2:A3,LARGE(IF($C$2:C3<>"",ROW($C$2:C3)),2)):INDEX($A$2:A3,MATCH(C3,$C$2:C3,0))),"") and press Enter, then drag the fill handle down to the last data row.
  4. 4. Verify the automatic subtotals: Check that each row next to a realized value now displays the sum of quantities since the previous realized value.
Automatically subtotal invoice quantities between nonblank realized values with one fill-down formula.Supports .xlsx format compatibility for editing Microsoft Excel workbooks without data loss.Familiar spreadsheet functions and a Microsoft Excel-like UI make migration easy.Free, lightweight, and avoids manual row-by-row summing.
microsoft office alternative - wps office

Frequently Asked Questions

What does this formula do in plain terms?

It sums the invoice quantities in Column A from the row after the previous nonblank realized value in Column C through the current row, so each realized row shows its own subtotal.

What should I do if my quantity column is not A or my realized-value column is not C?

Adjust the $A$2 and $C$2 references in the formula to match your actual quantity and realized-value columns before filling down.

Why do older spreadsheet versions require Ctrl+Shift+Enter for this formula?

Older versions evaluate LARGE, IF, and MATCH over a range and need the formula to be entered as an array formula. Excel 365, Excel 2021, and WPS versions with dynamic arrays can enter it normally.

Why does the first data row show an error or blank result?

The main fill-down formula assumes there is an earlier realized value. The first data row often needs a separate formula, such as =IF(C2<>"",A2,""), or an adjusted range.

Can I use this formula in an existing .xlsx file from Microsoft Excel?

Yes. Open the .xlsx file in WPS Spreadsheets, enter the formula, and fill it down; the workbook remains compatible with Microsoft Excel.