Do you know how to compare the sales during this week with the sales during the same days last week in Google Sheets? If not, in this tutorial – Same Days Last Week Comparison in Google Sheets – I am going to explain the same.

Please note that I am not talking about the comparison of the total/cumulative sales of this week with the total/cumulative sales of the last week.

I want to compare the sales of this Monday with the sales of last Monday, this Tuesday with the last Tuesday and so on.

Same day last week comparison is possible in Google Sheets with a few formulas.

If there are two or more sales on the same day we will summarize that before comparing. So you won’t face any issue regarding multiple data entry (sales) on the same day.

In some cases, there won’t be multiple data. For example, website traffic today vs website traffic the same day last week.

There won’t be multiple data because you might be only tracking data from Google Analytics, not from multiple sources like Facebook, Pinterest, etc.

What I am trying to say is, my same day last week comparison formula in Google Sheets will work irrespective of the number of records on any day.

Let’s learn more about the same day this week vs same day last week comparison in Google Sheets below.

This Week vs Same Days Last Week Comparison in Google Sheets (Step by Step Instructions)

Sample Data: To save your time, copy the data (or use the copy of the sheet itself) by following the below link.

Sample Sheet_Formulas

I hope you have copied the sheet. Let’s begin.

Filter This Week’s and Last Week’s Records (Data)

There is one important aspect to consider in our so-called same day last week comparison in Google Sheets. What’s that?

We can consider this week and last week based on week numbers or this 7 days vs last 7 days approach.

Didn’t get?

Based on Current and Previous Week Numbers

Assume today is Thu, 26 Mar 2020. Then the dates that fall in this week will be the dates from Sun, 22 Mar 2020 to Thu, 26 Mar 2020.

What about last week’s data?

No doubt, it will be from Sun, 15 Mar 2020 to Sat, 21 Mar 2020.

In my tutorial titled, Filter Data Based on This Week, Last Week, Last 30 Days in Google Sheets, I have explained the use of month numbers to filter this week’s as well as last week’s data.

Note: No need to insert the following 4 Filter formulas in your sheet. But you can insert it to see the results. We want these formulas (not all) later.

This Week (Formula # 1)

=filter(A2:B,Weeknum(A2:A)=weeknum(today()))

Last Week (Formula # 2)

=filter(A2:B,Weeknum(A2:A)=weeknum(today()-WEEKDAY(today())))

I will later use the below two filters (not the above ones) for my same day last week comparison formula in Google Sheets.

Based on This 7 Days vs Last 7 Days

As per this approach, if today’s date is Thu, 26 Mar 2020, this week will be from Fri, 20 Mar 2020 to Thu, 26 Mar 2020. Last week will be from Fri, 13 Mar 2020 to Thu, 19 Mar 2020.

This Week (Formula # 3):

=filter($A$2:$B,$A$2:$A>today()-7,$A$2:$A<=today())

Last Week (Formula # 4):

=filter($A$2:$B,$A$2:$A>today()-14,A2:A<=today()-7)

There are two formulas each for this week’s and last week’s filter. Choose which one you want. I’ll use option # 2, i.e. the formulas 3 and 4.

You can try both the options to see the changes in the output. I’ll tell you where you can use the above formulas.

Summarizing the Sales – This Week and Last Week

Now the next step in our same day this week vs same day last week comparison is summarizing the filtered data.

We can use the above formulas in Query as the ‘data’.

Syntax: QUERY(data, query, [headers])

We can use the Group By clause in Query to summarize the data. Read my guide – What is the Correct Clause Order in Google Sheets Query? – to get familiar with the said clause (grouping) and other clauses in Query.

This Week’s Sales Summary Formula in Cell D1: Here in this, the Query ‘data’ argument is the formula # 3 above.

=query(filter($A$2:$B,$A$2:$A>today()-7,$A$2:$A<=today()),"Select Col1, Sum(Col2) where Col1 is not null group by Col1 label Sum(Col2)'This Week', Col1'Date'",0)

Last Week’s Summary in Cell F1: Here in this, the Query ‘data’ argument is the formula # 4 above.

=query(filter($A$2:$B,$A$2:$A>today()-14,A2:A<=today()-7),"Select Col1, Sum(Col2) where Col1 is not null group by Col1")

We need one modification to the cell F1 formula. I’ll explain that below.

Vlookup for the Same Day Last Week Comparison in Google Sheets

We have this week’s dates in the array D2:D8 and last week’s date in the array F2:F8, right?

The array formula =ArrayFormula(D2:D-7) can populate the same dates that you can find in F2:F8. We will use this formula in Vlookup.

In cell F1, we can use a Vlookup formula by modifying the existing formula in that cell. In that Vlookup, the ‘search_keys’ will be the above array formula (see the paragraph above) and the ‘range’ argument will be the existing F1 formula.

Syntax: VLOOKUP(search_key, range, index, [is_sorted])

Generic Formula:

=ArrayFormula(vlookup(ArrayFormula(D2:D-7),F1_formula,2,0))

So it will be;

=ArrayFormula(vlookup(ArrayFormula(D2:D-7),query(filter($A$2:$B,$A$2:$A>today()-14,A2:A<=today()-7),"Select Col1, Sum(Col2) where Col1 is not null group by Col1"),2,0))

After adding the title (after further modification);

=ArrayFormula({"Last Week";ifna(Vlookup($D$2:$D-7,query(filter($A$2:$B,$A$2:$A>today()-14,A2:A<=today()-7),"Select Col1, Sum(Col2) where Col1 is not null group by Col1"),2,0))})

Percentage of Increase or Decrease in Comparison to Same Day Last Week

We have just completed our same day last week data comparison in Google Sheets. But I think the comparison won’t be complete without a column of data with the percentage of increase or decrease.

Assume the total sales on Fri, 20 Mar 2020 is 1323.00 and the same day last week, i.e. on Fri, 13 Mar 2020, is 283.00.

You can calculate the percentage of increase or decrease of the same day this week vs same data last week value as below.

=(1323-283)/283

The above is equal to 3.6749, which would be 367.49% if formatted to percentage. That means 1323 is a 367.49% increase of 283.

We can calculate the same using an array formula in cell G1 as below.

=ArrayFormula({"% of Decrease";if(D2:D="",,TO_PERCENT((E2:E-F2:F)/F2:F))})

Note: See the below calculation.

=(1323-283)/1323

It will return 78.61% that means 283 is a 78.61% decrease of 1323. If you want this in column G, use the below formula in G1.

=ArrayFormula({"% of Decrease";if(D2:D="",,TO_PERCENT((E2:E-F2:F)/E2:E))})

That’s all about the same day last week comparison in Google Sheets.

Resources

• How to Find Current Month’s Week Number In Google Sheets.
• Formula to Sum by Current Week in Google Sheets.
• Query to Calculate Hours Worked in Week Wise in Google Sheets.
• How to Create A Weekly Summary Report in Google Sheets.
• How to Count Orders Per Week in Google Sheets.
• Query to Create Daily/Weekly/Monthly/Quarterly/Yearly Report Summary in Google Sheets.
• Filter Data for Certain Number of Weeks in Google Sheets Using Formula.
• Find the Date or Date Range from Week Number in Google Sheets.
• Query to Sum Current Work Week Range in Google Sheets.
• How to Find Week Start Date and End Date in Google Sheets with Formula.
• How to Calculate the Moving Sum of Current Week in Google Sheets.
• Filter or Find Current Week This Year and Last Year in Google Sheets.
• SUMIF Formula to SUM Current Week’s Data in Google Sheets.
• SUMIF to Sum By Current Work Week in Google Sheets.
• Weekday Name to Weekday Number in Google Sheets.