Calculation Issues

Use Excel SUMIFS to Calculate the Previous Week's Total

John WilsonJohn Wilson Nov 18, 2026 868 views

Question details

Calculate a previous-week total automatically from dates and values in a worksheet.

Calculate the Previous Week's Total with Excel SUMIFS
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.
Before you start

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.

Solution 1Recommended

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.

1
Identify your ranges

In the worksheet, identify the date and value columns. The example uses dates in A2:A1000 and values in B2:B1000.

2
Enter the formula

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).

3
Adjust and verify

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.

Sum the Previous Monday-to-Sunday Week with SUMIFS
Different week definitions: This formula uses Monday as the first day of the week and TODAY() as the reference date. A Sunday-start week or a week based on the latest loaded date requires different criteria.
Calculate weekly totals

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. 1. Open your worksheet: Open WPS Spreadsheets and use Open to select the workbook containing your dates and values.
  2. 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. 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. 4. Adapt the ranges: Replace the example ranges with your worksheet ranges, then check the total against the previous Monday-through-Sunday rows.
Automatically calculate a previous-week total using date criteriaOpen and edit Microsoft Excel XLSX and XLS filesWork with a familiar spreadsheet interfaceUse WPS Office free for everyday spreadsheet tasks
microsoft office alternative - wps office

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.