To sum values for the current week, the first step is identifying dates that fall within the current week. Once identified, you can use various Google Sheets functions to calculate the sum. In this tutorial, you’ll learn how to accomplish this efficiently.

Your week might run from Sunday-Saturday or Monday-Sunday. The formulas provided can be adjusted accordingly.

Identify Dates in the Current Week

To find the start date of the current week, use this formula:

=TODAY()-WEEKDAY(TODAY(), 1)+1

This formula considers a Sunday-Saturday week. For a Monday-Sunday week, replace the 1 in the WEEKDAY function with 2. For more details about the WEEKDAY function, refer to my guide titled Google Sheets: The Complete Guide to All Date Functions.

To determine the end date of the current week, add 6 to the start date:

=TODAY()-WEEKDAY(TODAY(), 1)+1+6

Once you know the start and end dates of the current week, you can filter your data and calculate the sum for this period.

Note: You can also use the WEEKNUM function to identify the current week. However, if your dataset spans multiple years, the same week number may appear in different years, which can lead to incorrect filtering.

Sample Dataset

The dataset consists of the following columns:

• Column A: Invoice raised dates
• Column B: Invoice numbers
• Column C: Invoice amounts

The data range is A2:C, where A2:C2 contains headers. For calculations, we’ll use the range A3:C.

In cell D3, enter the formula to calculate the start date of the current week, i.e., =TODAY()-WEEKDAY(TODAY(), 1)+1.

The dataset provided highlights current-week dates, assuming the current date is 2024-12-03. You can adjust the dates in Column A as needed to test the formulas effectively.

Sum Current Week’s Data in Google Sheets: Multiple Methods

Here are four methods to sum the data for the current week. Among them, SUMIF and SUMPRODUCT are the simplest.

1. SUMIF Formula

Syntax:

SUMIF(range, criterion, [sum_range])

Formula:

=SUMIF(ISBETWEEN(A3:A, D3, D3+6), TRUE, C3:C)

• ISBETWEEN(A3:A, D3, D3+6) evaluates whether dates in A3:A fall between the start (D3) and end (D3+6) of the current week.
• The criterion is TRUE, and the sum_range is C3:C (invoice amounts).

2. SUMPRODUCT Formula

Syntax:

SUMPRODUCT(array1, [array2, …])

Formula:

=SUMPRODUCT(ISBETWEEN(A3:A, D3, D3+6), C3:C)

• ISBETWEEN(A3:A, D3, D3+6) is array1.
• C3:C is array2.
• This formula multiplies matching rows and sums the result.

3. SUM + FILTER Formula

Formula:

=SUM(FILTER(C3:C, ISBETWEEN(A3:A, D3, D3+6)))

• The FILTER function extracts rows from C3:C where the condition is TRUE.
• SUM then calculates the total of those filtered rows.

4. QUERY Formula

Among the four formulas, this one might be slightly complex for beginners to understand because it uses a text-based query language that resembles SQL. Syntax errors can occur if column references are not specified correctly.

Formula:

=QUERY(A3:C, "SELECT SUM(C) WHERE A >=DATE '"&TEXT(D3, "YYYY-MM-DD")&"' AND A <=DATE '"&TEXT(D3+6, "YYYY-MM-DD")&"' LABEL SUM(C)''")

• The QUERY function selects and sums column C from A3:C where the dates in column A are within the current week.
• TEXT(D3, "YYYY-MM-DD") formats the dates for the query.

Resources

• Filter or Find Current Week This Year and Last Year in Google Sheets
• How to Filter Current Week Horizontally in Google Sheets
• Filter Data by Week (This, Last, or N Weeks Ago) – Google Sheets
• Find the Date or Date Range from Week Number in Google Sheets