Calculation Issues

How to Calculate Cumulative Percentage Collections in Excel

John WilsonJohn Wilson Nov 10, 2026 869 views

Question details

The user needs to calculate monthly collections that represent 30% of the running cumulative total of bounced payment amounts.

How to Calculate Cumulative Percentage Collections in Excel
Product
Excel
Device & OS
not provided
Scenario
Financial tracking where current month collections are based on a fixed percentage of all accumulated bounced amounts from previous and current months.
Observed behavior
Requires a formula to properly sum cumulative totals month over month and multiply the result by a 30% factor.
Before you start

Ensure your data is organized in columns with headers, such as 'Month' in Column A and 'Total Bounced' in Column B, to apply the cumulative formulas correctly.

Solution 1Recommended

Calculate Cumulative Percentage Using SUM and Absolute References

The most efficient way to calculate a running cumulative total multiplied by a percentage is to lock the starting cell reference using the SUM function.

By anchoring the first cell in your range with a dollar sign ($), the SUM function will expand the calculation range automatically as you drag the formula down the column.

1
Enter the initial formula for the first month

Assuming your bounced amounts start in cell B2, click on cell C2 (the Collection column) and enter =B2*0.3 to calculate 30% for the first month.

2
Apply the cumulative formula for the second month

In cell C3, enter =SUM($B$2:B3)*0.3. The $B$2 locks the starting row, while B3 uses a relative reference that allows the range to expand downward.

3
Drag to copy the formula to remaining rows

Select cell C3, click and hold the fill handle (the small square in the bottom-right corner of the cell), and drag it down the column to apply the cumulative formula to subsequent months.

Calculate Cumulative Percentage Using SUM and Absolute References
Verify Percentage Multiplier: Check your specific business requirements to confirm whether the multiplier should be 0.3 (30%) or 0.35 (35%) before finalizing the workbook.
Efficient Spreadsheet Tool

Easily Calculate Cumulative Financial Data in WPS Spreadsheet

WPS Spreadsheet provides powerful formula calculation capabilities perfectly compatible with Microsoft Excel. You can quickly perform cumulative sum and percentage calculations to manage your financial tracking seamlessly.

  1. 1. Open your workbook: Launch WPS Spreadsheet and open your financial tracking document containing the bounced amounts.
  2. 2. Input the SUM formula: Type the cumulative SUM formula with absolute references, such as =SUM($B$2:B3)*0.3, into the target collection cell.
  3. 3. Use the fill handle: Drag the small square at the bottom right of the cell to fill the formula down your entire data column.
Fully compatible with Microsoft Excel formulas like SUM and absolute referencingIntuitive drag-and-fill handles for rapid data entry across columnsFree to use for everyday financial calculations and business reportsLightweight software with fast performance on large datasets
microsoft office alternative - wps office

Frequently Asked Questions

Why does my cumulative total not update when I drag the formula down?

This usually happens if you forget to use absolute cell references. Make sure the starting cell in your SUM range has dollar signs (e.g., $B$2) while the ending cell remains relative (e.g., B3) so the range expands dynamically.

How can I change the calculation to 35% instead of 30%?

Simply replace the 0.3 in your formula with 0.35. For example, change =SUM($B$2:B3)*0.3 to =SUM($B$2:B3)*0.35.

Can I use a separate cell for the percentage value instead of hardcoding it in the formula?

Yes, you can enter the percentage in a dedicated cell (e.g., E1). Update your formula to multiply by that cell and lock it using absolute references, like =SUM($B$2:B3)*$E$1. This makes it easier to update the percentage later.