How to Create a Horizontal Cumulative Sum Based on Criteria in Excel
Question details
The user needs to create a horizontal formula that calculates a cumulative sum for values matching specific date criteria, while returning 'n/a' for non-matching entries.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Building a horizontal spilled or non-spilled formula across columns to accumulate values based on matching date parameters.
- Observed behavior
- The formula should output the cumulative running sum exclusively for columns with matching dates and display 'n/a' or '0' for any non-matching entries.
Verify whether your spreadsheet software supports dynamic array functions if you plan to use the LET and SCAN methods. For older software versions, rely on the traditional SUMIF method instead.
Use LET and SCAN for a Dynamic Spilled Result
Create a single dynamic array formula that automatically spills horizontally without the need for manual dragging.
This modern method leverages dynamic array functions. By combining LET and SCAN, you can accumulate matching amounts into a dynamic array and efficiently return 'n/a' for non-matching dates.
Click on the first cell in the row where you want your horizontal cumulative results to begin.
Type the formula: =LET(criterion, date=initialDate, requiredAmount, IF(criterion, amount), accumulated, SCAN(0, requiredAmount, LAMBDA(x,y,SUM(x,y))), IF(criterion, accumulated, "n/a")). Replace the variable names with your actual ranges.
Press Enter. The calculation will execute and automatically spill horizontally across the adjacent columns to display your running totals.
Use Standard SUMIF and IF Functions
A traditional non-spilling method that works perfectly in all standard spreadsheet versions.
Easily Calculate Cumulative Sums with WPS Spreadsheets
WPS Spreadsheets provides comprehensive support for standard statistical functions and modern dynamic arrays, allowing you to seamlessly process complex horizontal data accumulations in just a few clicks.
- 1. Open your file in WPS Spreadsheets: Launch WPS Office and open the dataset containing your dates and numerical values.
- 2. Input the cumulative sum formula: Select the target cell and enter either your SUMIF combination or your advanced LET and SCAN dynamic array formula.
- 3. Execute and expand: Press Enter to compute the results. If using standard SUMIF, simply drag the fill handle horizontally to populate the remaining columns.
Frequently Asked Questions
Can I replace the 'n/a' result with a zero in the cumulative sum formula?
Yes, you can fully customize the output for non-matching criteria. Simply change the "n/a" string in the IF statement to a 0. For example, your formula would change to: =IF(A1=$F4,SUMIF($A1:A1,$F4,$A2:A2),0).
Why is my LET and SCAN formula returning a #SPILL! error instead of expanding horizontally?
A #SPILL! error occurs if there is existing data blocking the path where the dynamic array formula intends to expand. Clear the adjacent cells in that row so the formula has enough empty space to successfully display all the results.
What should I do if my spreadsheet version doesn't support the SCAN function?
If your software lacks support for modern dynamic array functions like SCAN and LET, you should use the traditional non-spilling method combining IF and SUMIF. Enter the formula for the first cell, then manually drag the fill handle horizontally across your columns.
