Fix SUMIFS EOMONTH Formulas That Exclude the Last Day in WPS Spreadsheets
Question details
How to correct a SUMIFS formula using EOMONTH when hidden time values cause records from the last day of the month to be excluded.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- You are using a SUMIFS formula to sum values from a start date through the end of a month, with EOMONTH used as the upper boundary.
- Observed behavior
- Records from the final day are omitted when the date cells contain times because EOMONTH returns the last day at midnight, so any later timestamp on that date is treated as outside the range.
Check whether the date column stores a time component by selecting a date cell and viewing the full value in the formula bar. Also confirm that the SUMIFS sum range and criteria range cover the same rows.
Use an exclusive upper boundary for the first day of the following month
Use this when hidden time values cause the EOMONTH upper boundary to exclude records from the last day of the month.
EOMONTH(R8,0) returns the last day of the month at midnight. Any timestamp later than midnight on that date is greater than the boundary and is omitted. Adding 1 to EOMONTH and using an exclusive less-than comparison includes all times on the final day while excluding the next month.
Click the cell containing the SUMIFS formula, then click inside the formula bar to edit it.
Change the end-date condition to use the first day after the month with an exclusive less-than operator: =SUMIFS($D:$D,$A:$A,">="&R8,$A:$A,"<"&EOMONTH(R8,0)+1).
Press Enter or click the check mark in the formula bar, then check that last-day records are included in the result.
Fix SUMIFS EOMONTH Last-Day Exclusion in WPS Spreadsheets
Open your workbook in WPS Spreadsheets and change the SUMIFS upper-bound condition to < EOMONTH(R8,0)+1 so the formula includes every timestamp on the last day while remaining compatible with Excel workbooks.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open the .xlsx workbook, and go to the sheet containing the SUMIFS formula.
- 2. Edit the SUMIFS formula: Select the formula cell and click in the formula bar to place the cursor in the existing formula.
- 3. Change the upper-bound criteria: Replace the end-date condition with "<"&EOMONTH(R8,0)+1 so the boundary is the first day after the month.
- 4. Press Enter and confirm: Press Enter, then spot-check a last-day record with a time value to confirm it is now included.
Frequently Asked Questions
Why does SUMIFS with EOMONTH exclude the last day of the month?
EOMONTH returns the last day at midnight. If the date cells contain a time value, those records are after midnight and are greater than the upper boundary, so they are excluded.
How do I include all times on the last day without including the next month?
Use an exclusive upper boundary set to the first day of the following month: =SUMIFS($D:$D,$A:$A,">="&R8,$A:$A,"<"&EOMONTH(R8,0)+1). This includes the entire final day and excludes the next month.
Does this formula work if my date cells have no time component?
Yes. Dates without times still work because they are less than the first day of the following month, so the formula works for both date-only cells and cells that contain times.
Can I use this SUMIFS and EOMONTH fix in WPS Spreadsheets with Excel files?
Yes. WPS Spreadsheets supports the same SUMIFS and EOMONTH syntax, so the corrected formula works in existing .xlsx workbooks from Microsoft Excel.
