How to Create a Cumulative Sum for Ascending or Descending Data in WPS Spreadsheets
Question details
The user needs a cumulative-sum formula that remains correct when data is sorted in ascending or descending order, without relative references shifting unexpectedly when copied.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Creating running totals for a column of values that may be sorted or filled in either direction.
- Observed behavior
- A relative-reference cumulative formula shifts its intended range when copied in the opposite direction, producing incorrect totals.
Open your workbook in WPS Spreadsheets and identify the value column and the first data cell to include in the running total. Decide whether you will fill the cumulative formula downward, upward, or in both directions.
Use INDEX to build a direction-independent cumulative sum range
Use this when you need one cumulative formula that stays correct whether you fill down or up, or when your data may be re-sorted.
Addition is commutative, so the total of a range is the same in either direction. The key is to anchor the starting cell with a fixed reference and let the ending cell follow the current row.
Click the cell where the running total should begin, such as B2, next to the first value in column A.
In the formula bar, type =SUM(INDEX($A:$A,MIN(ROW($A$2),ROW())):INDEX($A:$A,MAX(ROW($A$2),ROW()))) and adjust $A:$A and $A$2 to your value column and first data cell.
Press Enter to confirm, then drag the fill handle down or up. The formula keeps the range from the fixed starting row to the current row.
Use a mixed reference for a standard top-down running total
Use this when your data always starts at the same first row and you only fill the formula downward.
Create a Direction-Independent Cumulative Sum in WPS Spreadsheets
WPS Spreadsheets can directly build a running total with INDEX, SUM, ROW, MIN, and MAX, so your cumulative totals stay correct whether your data is sorted ascending or descending. The interface and formulas are Excel-compatible, so you can switch without losing work.
- 1. Open your workbook: Launch WPS Spreadsheets and open the file that contains the data you want to accumulate.
- 2. Select the first cumulative cell: Click the cell where the running total should begin, such as B2 next to the first data value.
- 3. Enter the dynamic cumulative formula: Type =SUM(INDEX($A:$A,MIN(ROW($A$2),ROW())):INDEX($A:$A,MAX(ROW($A$2),ROW()))) in the formula bar, replacing $A:$A and $A$2 with your column and first value cell.
- 4. Fill the formula in either direction: Press Enter, then use the fill handle to copy the formula down or up. The totals update according to the active row.
- 5. Save in Excel-compatible format: Click Menu > Save As and choose .xlsx so the workbook remains fully compatible with Microsoft Excel.
Frequently Asked Questions
Why does a copied cumulative sum formula shift when I fill it upward?
A relative reference such as A2 changes based on the direction and distance of the copy. Use a formula that locks the starting cell with $A$2 or uses INDEX with a fixed start row, so the range always begins at the intended cell.
Does the INDEX and SUM formula work for both ascending and descending data?
Yes. Summation is commutative, so order does not affect the total. The INDEX formula builds the range from the fixed starting row to the current row, regardless of whether you fill down or up.
What should I change in the formula for my own data?
Replace $A:$A with the column that contains the values and $A$2 with the first data cell in that column. For example, if values start in C5, use $C:$C and ROW($C$5).
Can I just use =SUM($A$2:A2) for a cumulative sum?
Yes, if you always fill downward from the top. However, it is not direction-independent; if you fill upward or re-sort in descending order, the relative A2 portion may create an unintended range.
Is this cumulative sum formula compatible with Microsoft Excel?
Yes. WPS Spreadsheets supports the same SUM, INDEX, ROW, MIN, and MAX functions, and the saved .xlsx file can be opened in Excel without replacing the formulas.
