How to Troubleshoot an Excel Rolling 7-Day Total SUMIFS Error
Question details
The user is experiencing an issue where a SUMIFS formula used to calculate a rolling 7-day total works on some rows but returns errors on others.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating a rolling 7-day total using the SUMIFS function across multiple rows of data.
- Observed behavior
- The formula calculates correctly for certain rows but produces an error when copied or applied to subsequent rows.
Before modifying your formulas, verify that the column containing your dates is formatted as actual Dates and not Text, as unrecognized text dates will cause SUMIFS criteria to fail.
Check Range Match and Relative References
Ensure all ranges in the SUMIFS formula are exactly the same size and use absolute references to prevent shifting.
The most common reason a SUMIFS formula works on the first row but fails on subsequent rows is shifting cell references. When criteria ranges are not locked, copying the formula down changes the evaluation area, which eventually causes range size mismatches.
Select the cell containing the SUMIFS error and press F2 (or double-click) to view the formula components in edit mode.
Check the sum_range and all criteria_range arguments to ensure they span the exact same row numbers (e.g., A2:A100 and B2:B100).
Highlight the range arguments in the formula bar and press F4 to add dollar signs (e.g., $A$2:$A$100), locking them in place before pressing Enter and dragging the formula down to other rows.
Validate Date Formats and Rolling Date Boundaries
SUMIFS requires date criteria to be properly formatted and evaluated as valid serial dates to accurately capture the rolling seven days.
Calculate Rolling Totals Effortlessly in WPS Spreadsheet
WPS Spreadsheet fully supports the SUMIFS function and advanced dynamic date calculations. It features powerful formula auditing tools to easily spot reference errors, making complex data analysis fast and accurate.
- 1. Open your data: Launch WPS Spreadsheet and open the workbook containing your daily data sets.
- 2. Select the target cell: Click the empty cell where you want to display the rolling 7-day total.
- 3. Enter the SUMIFS formula: Type the formula =SUMIFS(B:B, A:A, ">="&TODAY()-7) adjusting columns A and B for your specific date and sum ranges, and press Enter.
- 4. Lock and drag: If using specific ranges instead of whole columns, select the ranges in the formula bar, press F4 to lock them, then drag the fill handle down to calculate subsequent rows.
Frequently Asked Questions
Why does my SUMIFS return a #VALUE! error only on certain rows?
This usually happens because the sum range and criteria ranges are not exactly the same size, or you forgot to lock the ranges with absolute references (using $). When references are unlocked, they shift as you drag the formula down, eventually causing a range mismatch.
How do I write the criteria for the last 7 days in a SUMIFS formula?
You can use the TODAY() function combined with a greater-than-or-equal-to operator inside quotes. For example, use ">="&TODAY()-7 as the criteria argument to evaluate cells that contain dates occurring in the last 7 days.
Are there other functions to calculate a rolling total in Excel?
Yes, you can use a combination of the SUM and OFFSET functions to calculate dynamic rolling totals without date criteria. However, SUMIFS is generally the most efficient and readable method for processing date-based rolling totals.
