How to Use SUMIFS in WPS Spreadsheets for Weekly Reports Spanning Multiple Months
Question details
Calculate monthly totals from weekly reports in WPS Spreadsheets by summing column K where the week start date in column G is on or after the selected start date and the week end date in column I is on or before the selected end date.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user maintains weekly report records that span multiple months and needs a monthly total based on a selected start date and end date.
- Observed behavior
- The desired result is a SUMIFS total that includes only weekly records whose week starts on or after the selected start date and ends on or before the selected end date.
Confirm your weekly report contains the values to sum in column K, the week start dates in column G, and the week end dates in column I. Enter the selected start date in cell G1 and the end date in cell I1 so the SUMIFS formula can reference them.
Use SUMIFS to Sum Weekly Report Rows Within a Selected Date Range
Use this formula when you need a monthly total from weekly records and want rows included only when the week fully falls between the selected start and end dates.
The formula uses two date conditions. It checks that the week start in column G is greater than or equal to the start date in G1, and that the week end in column I is less than or equal to the end date in I1. Because the range references use absolute row anchors, the formula can be copied without shifting the lookup ranges.
Open your weekly report in WPS Spreadsheets and click the cell where you want the calculated monthly total to appear.
Type or paste the formula =SUMIFS(K$3:K$2001,$G$3:$G$2001,">="&$G$1,$I$3:$I$2001,"<="&$I$1) and confirm the range rows match your data.
Press Enter and check that the total includes only weekly rows whose start date is on or after G1 and whose end date is on or before I1.
Calculate Monthly Totals from Weekly Reports in WPS Spreadsheets
WPS Spreadsheets can directly calculate the monthly total using the SUMIFS formula with date criteria, so you do not need to manually filter or retype weekly data.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the weekly report workbook, and go to the sheet containing the data.
- 2. Set the date criteria cells: Enter the selected start date in cell G1 and the selected end date in cell I1.
- 3. Enter the SUMIFS formula: Click the output cell, type =SUMIFS(K$3:K$2001,$G$3:$G$2001,">="&$G$1,$I$3:$I$2001,"<="&$I$1), and press Enter.
- 4. Review the monthly total: Check the returned value against a few weekly rows to confirm the date range is applied correctly.
Frequently Asked Questions
How does this SUMIFS formula select the correct weekly rows?
It sums column K for every row where column G is greater than or equal to the date in G1 and column I is less than or equal to the date in I1. Rows whose week starts before G1 or ends after I1 are excluded.
Can I move the start and end date cells to another location?
Yes. If you move them, update the formula references from $G$1 and $I$1 to the new cells. Keep the operator concatenation such as ">="&$B$1 so WPS Spreadsheets treats the cell value as a date criterion.
Why are there dollar signs in K$3:K$2001 and $G$3:$G$2001?
The dollar signs lock the row numbers so the range references do not shift when you copy the formula. The date criteria cells $G$1 and $I$1 are fully absolute for the same reason.
What should I do if the formula returns zero?
Check that G1 and I1 are actual dates, not text, and that columns G and I use a recognized date format. Also verify that some weekly rows fall completely inside the selected range.
Does this SUMIFS formula work in Microsoft Excel?
Yes. WPS Spreadsheets supports the same SUMIFS syntax and workbook format, so the formula can be opened and calculated in Microsoft Excel or shared as an .xlsx file.
