How to Sum Amounts Between Two Dates Without Counting Blanks in WPS Office Spreadsheets
Question details
The user needs a formula that totals amounts in column O only when the date in column M falls within a specific month and year, ignoring blank date cells and dates from other years.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Calculating a monthly or yearly total from a data range with date and amount columns.
- Observed behavior
- A total must include only rows whose date is within the chosen month and year; blank date cells and dates from the same month in other years must be excluded.
Confirm that column M contains real dates or date serial numbers and that column O contains numeric values. If dates are stored as text, convert them to dates so SUMIFS can compare them correctly.
Use SUMIFS with inclusive start and exclusive end date boundaries
Use this fixed-month formula when you need a one-time total for a specific month and year, and want blank date cells excluded automatically.
The first criterion includes dates on or after the first day of the target month. The second criterion excludes dates on or after the first day of the next month, which prevents other years from slipping in. Blank date cells do not meet the lower-bound test, so they are ignored.
Open your workbook in WPS Office Spreadsheets and click the empty cell where you want the monthly total to appear.
In the Formula bar, type =SUMIFS(O5:O34,M5:M34,">="&DATE(2023,5,1),M5:M34,"<"&DATE(2023,6,1)). Adjust O5:O34 and M5:M34 to match your data range and the DATE year and month values to your target month.
Press Enter. The result sums only the amount rows whose date is from May 1, 2023 through May 31, 2023; blank date rows and other years are excluded.
Make the month and year dynamic with DATE, YEAR, and MONTH
Use this reusable formula when the target month or year may change and you want to update one helper cell instead of editing the formula.
Total Monthly Amounts by Date Range in WPS Office Spreadsheets
WPS Office Spreadsheets supports the same SUMIFS function, so you can directly apply this formula to total amounts between two dates while excluding blank date cells and other-year dates. Open your workbook in WPS Office, enter the formula, and get accurate monthly totals without switching tools.
- 1. Open your workbook: Launch WPS Office Spreadsheets and open the workbook that contains your date and amount data.
- 2. Select the result cell: Click the cell where you want the monthly total to appear.
- 3. Enter the SUMIFS formula: Type =SUMIFS(O5:O34,M5:M34,">="&DATE(2023,5,1),M5:M34,"<"&DATE(2023,6,1)) in the Formula bar and adjust the ranges and DATE values to your data.
- 4. Press Enter to calculate: Press Enter. WPS Spreadsheets returns the total for the specified month and year only.
Frequently Asked Questions
Why does this SUMIFS formula exclude blank date cells?
Blank cells do not satisfy the ">="&DATE(...) lower-bound criterion, so they fail the date test and are not included in the sum. The exclusive upper-bound criterion further isolates the target month.
Can I use cell references instead of a hard-coded DATE function?
Yes. Put any date from the target month in a helper cell such as G1, then use DATE(YEAR(G1),MONTH(G1),1) and DATE(YEAR(G1),MONTH(G1)+1,1) as the boundaries so the formula updates when G1 changes.
How do I prevent dates from the same month in other years from being included?
Use DATE(2023,5,1) as the start and DATE(2023,6,1) as the exclusive end. This restricts the range to May 2023 only, so May 2022 or May 2024 falls outside the date bounds.
What should I check if my SUMIFS formula returns 0 or an error?
Confirm that the date cells are real dates or serial numbers rather than text, ensure the amount cells are numeric, and check that the O and M range sizes in SUMIFS are the same.
