Sum Values Between Consecutive Occurrences of a Criterion in Excel
Question details
Calculate the sum of numerical values located between recurring specific text markers in a dataset.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- A user has a table containing numeric amounts and text markers (e.g., "apple"). They need to dynamically sum the values starting from each marker up until the row preceding the next occurrence of that same marker.
- Observed behavior
- The user wants a dynamic formula to automatically find these consecutive blocks and output the calculated sum for each group directly next to the marker.
Ensure your dataset is logically organized without merged cells in the target columns, and verify that your spreadsheet software supports modern dynamic array functions such as LET, MAP, and XMATCH.
Use Dynamic Array Formulas (LET, MAP, and XMATCH)
By combining modern dynamic array functions, you can create a single spilling formula that evaluates each row, detects the marker, calculates the range up to the next marker, and sums the values.
This solution utilizes Microsoft 365 dynamic arrays. The LET function simplifies the logic by naming our variables, while MAP evaluates each row individually. XMATCH searches for the next instance of your criterion, defining the precise window of cells to pass into the SUM function.
Click on the top cell of the column where you want the calculated sums to appear (e.g., cell C2).
Type or paste the following formula: =LET(amount,$A$2:$A$32,values,$B$2:$B$32,stop,"apple",size,ROWS(amount),id,SEQUENCE(size),MAP(id,LAMBDA(j,IF(INDEX(amount,j)=stop,LET(next,XMATCH(stop,DROP(amount,j)),window,SEQUENCE(IFNA(next,size-j+1),,j),SUM(INDEX(values,window))),""))))
Change $A$2:$A$32 to match the column containing your criteria markers. Change $B$2:$B$32 to the column containing the numerical values. Replace "apple" with your exact criterion text.
Press Enter. The formula will automatically calculate and spill the sums down the column adjacent to each marker occurrence.
Perform Complex Data Grouping and Formulas in WPS Spreadsheet
WPS Spreadsheet offers powerful data calculation capabilities, supporting advanced formulas and functions. Whether you're summing between consecutive criteria or analyzing massive datasets, WPS provides a smooth, highly compatible experience.
- 1. Open your data file: Launch WPS Spreadsheet and open your .xlsx data file.
- 2. Apply your formulas: Select the target cell, enter your advanced formula, and press Enter to calculate.
- 3. Save with perfect compatibility: Save your workbook natively in .xlsx format to share seamlessly with Excel users.
Frequently Asked Questions
What if my version of Excel doesn't support the LET or MAP functions?
If you are using an older version like Excel 2016 or 2019, dynamic array functions are not available. You will need to use a helper column to assign group IDs to each block using an IF statement, and then use a standard SUMIF function to total the values for each group ID.
Why does my formula return a #SPILL! error?
A #SPILL! error occurs when the dynamic array formula tries to output its results into multiple cells, but one or more of those destination cells already contain text or data. Clear any existing data in the cells below your formula to resolve this.
Can I sum values between two different criteria instead of the same repeating marker?
Yes, you can modify the XMATCH function within the LET formula to search for a secondary "end" marker text rather than looking for the next occurrence of the initial "start" marker.
