Use Excel SUMIFS to Calculate the Previous Week's Total
Question details
Calculate a previous-week total automatically from dates and values in a worksheet.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- New weekly data is added to a date-based worksheet, and the user wants a formula that references the previous week.
- Observed behavior
- The desired result is a total that updates for the previous week using the chosen definition of a week.
Confirm which day starts your week and whether the previous week means the calendar week before the current week. Check that the date column contains actual spreadsheet dates and identify the range of values to sum.
Sum the Previous Monday-to-Sunday Week with SUMIFS
Use this formula when the previous week means the completed Monday-to-Sunday calendar week before today.
SUMIFS applies both a start-date and an end-date condition. The formula includes the previous Monday and excludes the current Monday, so it also handles dates that contain times.
In the worksheet, identify the date and value columns. The example uses dates in A2:A1000 and values in B2:B1000.
Select the cell where you want the total and enter =SUMIFS(B2:B1000,A2:A1000,">="&TODAY()-WEEKDAY(TODAY(),2)-6,A2:A1000,"<"&TODAY()-WEEKDAY(TODAY(),2)+1).
Replace both A2:A1000 references and B2:B1000 with your matching date and value ranges. Check the result against the rows dated from the previous Monday through Sunday, and extend the ranges when new rows fall outside them.

Calculate the Previous Week's Total in WPS Spreadsheets
Use SUMIFS, TODAY and WEEKDAY in WPS Spreadsheets to total values from the previous Monday-to-Sunday week.
- 1. Open your worksheet: Open WPS Spreadsheets and use Open to select the workbook containing your dates and values.
- 2. Check the data ranges: Locate the date column and the values to total. For the example below, use dates in A2:A1000 and values in B2:B1000.
- 3. Enter SUMIFS: Select an empty cell and enter =SUMIFS(B2:B1000,A2:A1000,">="&TODAY()-WEEKDAY(TODAY(),2)-6,A2:A1000,"<"&TODAY()-WEEKDAY(TODAY(),2)+1).
- 4. Adapt the ranges: Replace the example ranges with your worksheet ranges, then check the total against the previous Monday-through-Sunday rows.

Frequently Asked Questions
Why use SUMIFS instead of SUMIF for the previous week?
SUMIFS lets you apply both the start and end date to the same date column. This limits the total to one completed calendar week.
Will the formula include dates with times?
Yes. The formula includes values on or after the previous Monday and before the current Monday, including timestamps on the previous Sunday.
Does the formula use the latest date in my data?
No. TODAY() uses the current date, so the formula totals the calendar week before today. If your latest loaded data is older, you need criteria based on a different reference date.
Why are newly added rows missing from the total?
Check whether the new rows fall outside A2:A1000 or B2:B1000. Edit the formula to extend both ranges while keeping them the same size.




