Calculate Rolling 7-Day Spending and Unique Cards in Excel
Question details
The user wants to calculate the total amount spent and count the distinct number of cards used by each customer within a rolling 7-day window.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Financial or transaction data analysis where rolling metrics and distinct counting are required.
- Observed behavior
- Requires a formula-based approach to extract a rolling sum and a distinct count of values from a transaction dataset.
Ensure your transaction data is formatted as an Excel Table or use absolute references for your ranges to prevent shifting errors when you copy the formulas down the column.
Use Dynamic Arrays in Excel 365 or 2021
Use modern dynamic array functions like FILTER and UNIQUE alongside SUMIFS for a straightforward and efficient calculation.
In newer versions of Excel, dynamic array functions make it easy to filter data on the fly and extract unique values without needing complex legacy array formulas.
Select the target cell and use the SUMIFS function. Assuming Column A is Customer, B is Date, and C is Amount, type: =SUMIFS(C:C, A:A, A2, B:B, ">="&(B2-7), B:B, "<="&B2). Press Enter to calculate the rolling sum.
In the adjacent cell, combine COUNTA, UNIQUE, and FILTER. Assuming Column D holds the Card IDs, type: =COUNTA(UNIQUE(FILTER(D:D, (A:A=A2)*(B:B>=(B2-7))*(B:B<=B2)))).
Select both formula cells, click the fill handle in the bottom-right corner, and drag it down to apply the calculations to all rows in your transaction list.
Use Array Formulas for Older Excel Versions
If you are using Excel 2019 or older, you must rely on traditional array formulas using SUM, IF, and COUNTIFS.
Easily Calculate Rolling Data and Distinct Counts in WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like FILTER and UNIQUE, as well as classic formulas like SUMIFS. You can perform complex data analysis and dynamic calculations seamlessly without worrying about version limitations.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office and open your transaction workbook.
- 2. Enter the SUMIFS formula: Select the cell for your 7-day spending total, enter the SUMIFS formula based on your date and customer criteria, and press Enter.
- 3. Use dynamic arrays for unique cards: In the next column, use the COUNTA, UNIQUE, and FILTER combination to instantly extract and count the distinct card numbers.
- 4. Drag to fill: Double-click the fill handle on the selected cells to automatically copy the rolling calculations down the entire column.
Frequently Asked Questions
Why is my unique card count formula returning a #CALC! error?
The #CALC! error typically occurs in the FILTER function when it returns an empty array (meaning no data matches your criteria). You can fix this by wrapping your FILTER function inside an IFERROR or using the [if_empty] parameter inside FILTER, like so: FILTER(range, criteria, "").
Does a rolling 7-day period include the current day?
Yes, if your formula uses "<=" for the current date and ">=" for Date-7, it counts a full 7-day window inclusive of the target dates. You can adjust the subtraction (e.g., Date-6) depending on your exact business definition of a rolling 7-day period.
How do I correctly enter an array formula in older versions of Excel?
After typing the entire formula in the formula bar, do not just press Enter. Instead, press Ctrl + Shift + Enter simultaneously. Excel will automatically add curly braces {} around your formula to indicate it is being processed as an array formula.
