Formula Errors

How to Find the First Six-Cell Excel Total Greater Than $550,000

Huda Qurayshi Sep 27, 2026 868 views

Question details

The user needs to calculate a rolling sum for every six consecutive cells in a horizontal row and pinpoint the first instance where this combined total exceeds $550,000.

How to Find the First Six-Cell Rolling Sum Greater Than $550,000 in Excel

Product: Excel
Device & OS: not provided
Scenario: Performing financial forecasting, quarterly budget tracking, or sales analysis where a specific financial threshold must be identified within a moving time window.
Observed behavior: A dynamic array formula is required to scan the dataset, calculate consecutive six-cell totals, and return either the column header or the specific total value of the first qualifying group.

Before you start

Ensure your numerical data is arranged horizontally in a continuous row (e.g., B2:S2) without blank cells, and that the corresponding labels or time periods are aligned in the row directly above it.

Solution 1Recommended

Use LET and MAKEARRAY for a True Six-Cell Rolling Sum

This solution builds a custom array of six-cell rolling sums using modern dynamic array functions, making it perfect for finding exact moving time windows.

This formula uses MAKEARRAY to dynamically generate all possible 6-cell windows within your range. It then evaluates each window to see if its sum exceeds your threshold, returning the header label of the first matching window.

Select the output cell

Click on the cell where you want to display the time period or label where the $550,000 threshold is first crossed.

Input the LET formula

Type the following formula into the formula bar: =LET(data,B2:S2,windows,MAKEARRAY(1,COLUMNS(data)-5,LAMBDA(r,c,SUM(INDEX(data,1,c):INDEX(data,1,c+5)))),INDEX(B1:S1,1,XMATCH(TRUE,windows>550000,0)))

Execute the formula

Press Enter. The formula will calculate the moving sums and return the starting label (e.g., Q1 or Month 1) of the first six-cell group that exceeds 550,000.

Use LET and MAKEARRAY for a True Six-Cell Rolling Sum

Return the actual total instead of the label: If you want to see the specific rolling sum value rather than the header label, change the final INDEX part of the formula to: =INDEX(windows,1,XMATCH(TRUE,windows>550000,0))

Solution 2

Use SCAN and XMATCH for Cumulative Totals

If your goal is to find where the absolute cumulative sum (running total from the start) first exceeds $550,000, the SCAN function is highly efficient.

Advanced Data Analysis Made Easy

Calculate Complex Rolling Sums Effortlessly with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions like LET, MAKEARRAY, SCAN, and XMATCH. You can process heavy financial models and rolling sum calculations instantly without any performance lag.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your financial workbook containing the data series.
2. Select your target cell: Click on any empty cell where you want the rolling sum threshold result to appear.
3. Paste the dynamic formula: Copy the provided LET and MAKEARRAY formula and paste it into the WPS formula bar.
4. View your results instantly: Press Enter to instantly execute the array formula and reveal the time frame where your $550,000 target was exceeded.

100% compatibility with Microsoft Excel dynamic array formulasEasily calculate 6-cell rolling sums with zero syntax changesLightweight program offering smooth performance for large datasetsFree to use with an intuitive, tabbed user interface

microsoft office alternative - wps office

Frequently Asked Questions

Can I modify the formula for a 12-cell rolling sum instead of six?

Yes. In the LET formula, change the '-5' in COLUMNS(data)-5 to '-11', and change the '+5' in INDEX(data,1,c+5) to '+11'. This adjusts the window size to calculate 12 consecutive cells.

What happens if no 6-cell window exceeds $550,000?

The XMATCH function will not find the TRUE condition, resulting in an #N/A error. You can wrap the entire formula in an IFERROR function, like =IFERROR(your_formula, "Target not met"), to display a custom message instead.

Why is my formula returning a #NAME? error?

The #NAME? error typically occurs if you are using an older version of Excel that does not support modern dynamic array functions like LET, MAKEARRAY, or XMATCH. You need Microsoft 365, Excel 2021, or the latest version of WPS Office to execute these functions.

How do I change the threshold amount in the formula?

Simply locate the 'windows>550000' segment in the formula and replace '550000' with your desired numeric target (e.g., 'windows>1000000' for a 1 million threshold). Do not include currency symbols or commas in the number.

Excel & Data Problems