Create a Fillable Excel Formula to Sum Values Between Break Rows in WPS Office
Question details
The user wants a fillable formula that sums values in Column 5 within sections delimited by Break markers in Column 6, applies a time condition from Column 8, and excludes gray date-total rows.
- Product
- WPS Office Excel / Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is setting up a reusable summary formula for a worksheet that contains break-separated groups and date-total rows.
- Observed behavior
- The formula must conditionally total Column 5 only for non-break, non-date-total rows that meet the time condition from Column 8.
Confirm how Break markers are exactly written in Column 6, how the 00:00:00 time is stored in Column 8, and how gray date-total rows are labeled. Formulas cannot read cell fill color, so gray rows must be identified by text, value, or a helper column.
Use helper columns with SUMIFS to sum values between Break markers
This is the most maintainable approach when you need a fillable formula and the worksheet has consistent Break markers and time values.
Excel formulas cannot use gray fill color as a condition unless you add VBA or manually flag the rows. A helper column makes the rule explicit and keeps the final SUMIFS formula readable and editable.
Open the target workbook in WPS Office Excel and save a copy if you want to keep the original layout intact.
Insert a new helper column, for example Column I, and enter =IF(F2="Break",N(I1)+1,N(I1)) in I2. Fill this formula down to give each segment between Break markers a unique group ID.
Insert another helper column, such as Column J, and enter =IF(OR(F2="Break",H2=TIME(0,0,0),ISNUMBER(SEARCH("total",A2))),0,1) in J2. Adjust the total-row label test to match your gray date-total rows, then fill down.
In the cell where you want the result, enter =SUMIFS(E:E,I:I,1,H:H,TIME(0,0,0),J:J,1) and replace the group number 1 with the segment you want to total.
Use WPS Office Excel to Build a Fillable SUMIFS Formula for Break-Separated Totals
WPS Spreadsheets supports the same SUMIFS, SUMPRODUCT, IF, and helper-column techniques used in Excel. You can create a reusable formula that totals Column 5 between Break markers, applies the time condition from Column 8, and excludes gray date-total rows once those rows are identified by a helper flag.
- 1. Download and open WPS Office: Download WPS Office, install it, and open your Excel workbook in WPS Spreadsheets.
- 2. Add helper columns: Insert one group ID column and one include flag column next to your data so the formula can identify Break segments and rows to ignore.
- 3. Write the SUMIFS formula: Enter =SUMIFS(E:E,I:I,1,H:H,TIME(0,0,0),J:J,1) in the desired report cell, changing the group number and column letters to match your layout.
- 4. Test and save: Check totals for a few known groups, adjust the helper formula if Break markers or total-row labels differ, then save the workbook as .xlsx.
Frequently Asked Questions
Can an Excel formula ignore gray date-total rows automatically?
No. Standard formulas cannot read cell fill color. In WPS Office Excel, add a helper column that flags date-total rows by their text, value, or another column, then use that helper column in SUMIFS.
How do I create a segment ID between Break markers?
Add a helper column and fill down =IF(F2="Break",N(I1)+1,N(I1)). This increments the group number each time Column F contains Break, so each segment can be totaled separately.
What formula applies the 00:00:00 time condition from Column H?
Use TIME(0,0,0) as the equality condition inside SUMIFS, for example H:H,TIME(0,0,0). If Column H stores text instead of real time, compare it to the text "00:00:00".
Do I really need to share a sample workbook?
For an exact formula, yes. The row structure, total-row labels, and how gray formatting is applied are not fully described, so a sample allows a reliable formula to be written and tested.
