I think this is a new concept. Instead of using week numbers (the numbers representing weeks in a whole year), we can summarize data by week start and end dates in Google Sheets.

Honestly, I didn’t see this type of data summation in any spreadsheet applications so far.

For the weekly summary, everybody depends on the WEEKNUM function. But here I am going to use WEEKDAY instead!

In Google Sheets, we use the WEEKNUM function with QUERY function to very easily create a weekly summary report. Of course, we can use SUMIF together with WEEKNUM too.

Before going to how to summarize data by week start and end dates, here are two of my WEEKNUM based tutorials for learning sum by week.

1. How to Create A Weekly Summary Report in Google Sheets.
2. Query to Create Daily/Weekly/Monthly/Quarterly/Yearly Report Summary in Google Sheets.

It’s actually quite simple to summarize data by week start and end dates in Google Sheets. You just want to know how to convert a date to the corresponding calendar week.

Didn’t get?

WEEKDAY Logic

Let’s consider the date 01-Aug-2020.

By using the WEEKDAY function, we should know how to convert this date to 27-Jul-2020 to 02-Aug-2020, the week in which the said date falls. Then preparing the summary report would be quite easy.

Note: Here the week is based on Monday-Sunday. We can change that too as per our requirement.

Let’s see how to do this and summarize data by week start and end dates in Google Sheets.

Please follow my step-by-step instructions below.

Formula to Summarize Data by Week Start and End Dates in Google Sheets

There are only two major steps involved. They are the use of WEEKDAY and QUERY.

Note:

1. For sample data, please refer to the array A2:B20 on the image above.
2. Wherever the result of any formula returns date values instead of dates or dates instead of numbers, you can select that range and format back to the correct format from the format menu (Format > Number > Date or Format > Number > Number).

Data Preparation for the Summary Using Weekday

We will first use two helper columns, i.e. columns C and D (arrays C2:C20 and D2:D20)- for data preparation. Later we can remove these two arrays.

In cell C2, enter the below WEEKDAY based formula.

=ArrayFormula(
     A2:A20-
     WEEKDAY(A2:A20,2)+1
)

Here is one more Weekday formula that to be entered in cell D2.

=ArrayFormula(
     A2:A20-
     WEEKDAY(A2:A20,2)+7
)

What do these two formulas do?

The formulas check the dates in A2:A20 and then return the corresponding week starting dates in the array C2:C20 and week ending dates in the array D2:D20.

Formula Explanation

To learn the formula we should start with the syntax of the WEEKDAY function as it’s the core function in the formula.

The ArrayFormula is for using the said function within an array of dates.

WEEKDAY(date, [type])

The WEEKDAY returns the number representing the day of the week of the ‘date’ argument. I’ll come to the ‘type’ argument later.

In cell A2 the date is 01/Aug/2020.

The day of the week of this date is “Saturday”.

If the week starts from Monday to Sunday, then the day of the week number of this date would be 6.

=weekday(A2,2)

The number 2 is the ‘type’ which says the week starts from Monday to Sunday. You may please read my date functions guide to know more about the ‘type’ argument.

If you deduct the number 6 from the date in cell A2 and then add 1 to it, you will get week start date.

=A2-weekday(A2,2)+1

If you add 7, then you will get the week end date.

=A2-weekday(A2,2)+7

Still having doubt? Here is my specific tutorial on this – How to Find Week Start Date and End Date in Google Sheets with Formula.

We have completed the major part in writing our formula to summarize data by week start and end dates in Google Sheets.

QUERY to Summarize Data by Week Start and End Dates in Google Sheets

For the time being, let’s keep those helper columns.

Here I am going to use the Google Sheets QUERY function, the function that makes Google Sheets one of the most powerful spreadsheet applications, to summarize our data by the week start and end dates in Google Sheets.

In cell F2, enter the below QUERY formula which uses the data in the helper columns C and D.

FORMULA # 1: Finite Range and Helper Columns

=query(
     {C2:D20,B2:B20},
     "Select Col1,Col2,sum(Col3) group by Col1,Col2 label sum(Col3)''"
)

Formula Explanation

{C2:D20,B2:B20} – Query Data

There are three columns in this QUERY data – Week start dates, Week end dates, and the Amount column.

The QUERY sums the column 3 (Amount) by grouping the columns 1 (Week starts) and 2 (Week Ends).

Removing Helper Columns and Makes the Range Infinite (Open)

I hope you have learned how to summarize data by week start and end dates in Google Sheets in a finite (closed range).

Now here are the steps to remove the helper columns and use the formula in an infinite (open) range.

To remove the helper columns, first, combine the corresponding two formulas in C2 and D2 as below.

Cut D2 (right-click and select Cut or Ctrl+X) formula and then modify the C2 formula by joining the D2 formula with it.

=ArrayFormula(
     {(A2:A20-WEEKDAY(A2:A20,2)+1),
     (A2:A20-WEEKDAY(A2:A20,2)+7)}
)

Then replace the range C2:D20 in Query formula with the just above formula.

FORMULA # 2: Finite Range but No Helper Columns

=ArrayFormula(
     query(
        {
           {(A2:A20-WEEKDAY(A2:A20,2)+1),
           (A2:A20-WEEKDAY(A2:A20,2)+7)},
           B2:B20
        },
        "Select Col1,Col2,sum(Col3) group by Col1,Col2 label sum(Col3)''"
     )
)

Now you feel free to remove/empty columns C and D (column D may already blank as we have cut the formula).

Now the next step is how to make the range infinite by changing A2:A20 and B2:B20 with A2:A and B2:B. Here are the changes required.

In the above formula, replace (A2:A20-WEEKDAY(A2:A20,2)+1) with if(datevalue(A2:A)>0,A2:A-WEEKDAY(A2:A,2)+1).

Similarly you may replace (A2:A20-WEEKDAY(A2:A20,2)+7) with if(datevalue(A2:A)>0,A2:A-WEEKDAY(A2:A,2)+7).

If the values in A2:A are dates, the WEEKDAY formula will execute in those rows and it will skip blanks. The DATEVALUE helps identify the rows containing dates.

Then modify B2:B20 to B2:B.

You are almost ready.

Since the formula is for infinite rows, include the WHERE clause "where Col1 is not null" before the GROUP BY clause in QUERY. So the final formula;

FORMULA # 3: Infinite Range Without Helper Columns

=ArrayFormula(
     query(
        {
           {if(datevalue(A2:A)>0,A2:A-WEEKDAY(A2:A,2)+1),
           if(datevalue(A2:A)>0,A2:A-WEEKDAY(A2:A,2)+7)},
           B2:B
        },
        "Select Col1,Col2,sum(Col3) where Col1 is not null group by Col1,Col2 label sum(Col3)''"
     )
)

You May Also Like: What is the Correct Clause Order in Google Sheets Query?

Here is one additional tip.

Summarize Data by Week Start and End Dates that Includes Zero Transaction Weeks

The result that you get from the above formula (result shown on image # 3) may have some differences with the result I have shown at the beginning of this post (image # 1).

There (on image # 1) you can see week start and end dates in chronological order without missing any weeks.

I mean we will take the min date and max date in A2:A and generate all the weeks during that period. As a result, there would be some weeks with 0 amount. Those are zero transaction weeks.

How do we achieve that?

Let’s start from the beginning.

Copy just the sample data from A2:B20 to a new sheet. In that sheet we can write our new formula.

Step # 1

Insert the following formulas in cell C2;

=sequence(
     days(
        max(A2:A),min(A2:A)
     )+1,
     1,
     min(A2:A)
)

D2;

=ArrayFormula(
     C2:C-
     WEEKDAY(C2:C,2)+1
)

and

E2.

=ArrayFormula(
     C2:C-
     WEEKDAY(C2:C,2)+7
)

The C2 formula will expand the dates from the Min date in A2:A to the Max date in A2:A. The D2 and E2 formula returns the week start and end dates of the expanded dates.

Step # 2

We can replace C2:C in the above last two formulas with the corresponding formula in C2.

So the formula in D2 will be;

=ArrayFormula(
     sequence(
        days(max(A2:A),min(A2:A))+1,1,min(A2:A)
     )-
     WEEKDAY(
        sequence(
           days(max(A2:A),min(A2:A))+1,1,min(A2:A)
        ),2
     )+1
)

and E2 will be;

=ArrayFormula(
     sequence(
        days(max(A2:A),min(A2:A))+1,1,min(A2:A)
     )-
     WEEKDAY(
        sequence(
           days(max(A2:A),min(A2:A))+1,1,min(A2:A)
        ),2
     )+7
)

Now modify the formula in C2 as below.

=ArrayFormula(
     sequence(
        days(max(A2:A),min(A2:A)
        )+1,
        1,
        min(A2:A)
     )*0
)

It would return a column with 0 values. What’s the purpose of all these?

If you follow the steps one by one as above and follow the coming few steps, you will understand it without my explanation. So let’s go to the next step.

Step # 3

Let’s combine the above three columns as per the below generic formula

Generic Formula:

=ArrayFormula({D2:D,E2:E,C2:C})

This will return the values in the following format – Week start dates, Week end dates, and A column with 0s.

=ArrayFormula(
     {sequence(days(max(A2:A),min(A2:A))+1,1,min(A2:A))-
     WEEKDAY(
        sequence(days(max(A2:A),min(A2:A))+1,1,min(A2:A)),2
     )+1,
     sequence(days(max(A2:A),min(A2:A))+1,1,min(A2:A))-
     WEEKDAY(
        sequence(days(max(A2:A),min(A2:A))+1,1,min(A2:A)),2
     )+7,
     sequence(days(max(A2:A),min(A2:A))+1,1,min(A2:A))*0}
)

Final Step to Summarize Data by Week Start and End Dates in Google Sheets

Here we can use the same Formula # 3 which we have used to summarize data by week start and end dates (chronological order but only with transaction weeks).

In that formula, you just need to add the above formula as additional rows with the Query ‘data’. That means, simply put a semicolon after B2:B and add the above formula.

=ArrayFormula(
     query(
        {{if(datevalue(A2:A)>0,A2:A-WEEKDAY(A2:A,2)+1),
        if(datevalue(A2:A)>0,A2:A-WEEKDAY(A2:A,2)+7)},
        B2:B;
        {sequence(days(max(A2:A),min(A2:A))+1,1,min(A2:A))-
        WEEKDAY(
           sequence(days(max(A2:A),min(A2:A))+1,1,min(A2:A)),2
        )+1,
        sequence(days(max(A2:A),min(A2:A))+1,1,min(A2:A))-
        WEEKDAY(
           sequence(days(max(A2:A),min(A2:A))+1,1,min(A2:A)),2
        )+7,
        sequence(days(max(A2:A),min(A2:A))+1,1,min(A2:A))*0}},
        "Select Col1,Col2,sum(Col3) where Col1 is not null group by Col1,Col2 label sum(Col3)''"
     )
)

Sample_Sheet_1920

That’s all. Enjoy!