Do you want to compare the sales of this week with the sales of the same days last week in Google Sheets? If so, you’ve come to the right place! In this tutorial, I will show you how to perform a same day last week comparison in Google Sheets.
Please note that we’re not talking about comparing the total/cumulative sales of this week with the total/cumulative sales of last week. Instead, we want to compare the sales of this Monday with the sales of last Monday, this Tuesday with the sales of last Tuesday, and so on.
The great news is that same day last week comparison is possible in Google Sheets with just a few simple formulas. Even if you have multiple sales on the same day, we will summarize them before comparing. So you won’t face any issues with multiple data entries. However, in some cases, there won’t be multiple data, such as website traffic today compared to website traffic on the same day last week.
No matter the number of records on any given day, my same day last week comparison formula in Google Sheets will work like a charm.
This Week vs Same Days Last Week Comparison in Google Sheets (Step by Step Instructions)
To save you time, let’s start with some sample data. You can copy the data or use a copy of the sheet by following the link.
Alright, now that you have the sheet, let’s get started!
Filter This Week’s and Last Week’s Records (Data)
There is one important aspect to consider in our same day last week comparison in Google Sheets. We can consider this week and last week based on week numbers or a “this 7 days vs last 7 days” approach.
If we use the week numbers approach, we can filter this week’s data as the dates falling between Sunday, March 22, 2020, and Thursday, March 26, 2020. Similarly, last week’s data would be from Sunday, March 15, 2020, to Saturday, March 21, 2020.
Alternatively, we can use the “this 7 days vs last 7 days” approach. In this case, if today’s date is Thursday, March 26, 2020, this week would be from Friday, March 20, 2020, to Thursday, March 26, 2020. Last week would be from Friday, March 13, 2020, to Thursday, March 19, 2020.
Summarizing the Sales – This Week and Last Week
Now that we have filtered the data for this week and last week, the next step is to summarize the sales. We can do this using the QUERY formula in Google Sheets.
Here’s the formula to summarize this week’s sales in cell D1:
=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)And here’s the formula to summarize last week’s sales in cell F1:
=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")Vlookup for the Same Day Last Week Comparison in Google Sheets
To perform the same day last week comparison, we can use a Vlookup formula. In this case, the ‘search_keys’ will be the array formula ArrayFormula(D2:D-7) (which populates the same dates as in F2:F8), and the ‘range’ argument will be the existing F1 formula.
Here’s the generic formula for the Vlookup:
=ArrayFormula(vlookup(ArrayFormula(D2:D-7),F1_formula,2,0))After adding the title, it will be:
=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
To make the comparison more meaningful, let’s calculate the percentage of increase or decrease in the data. For example, if the total sales on Friday, March 20, 2020, is 1,323.00 and the sales on Friday, March 13, 2020, is 283.00, we can calculate the percentage of increase or decrease.
The formula to calculate the percentage of increase or decrease is:
=(1323-283)/283To calculate this using an array formula in cell G1, use the following formula:
=ArrayFormula({"% of Decrease";if(D2:D="",,TO_PERCENT((E2:E-F2:F)/F2:F))})That’s it! You’ve successfully performed 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
Remember, if you need any further assistance, don’t hesitate to visit Crawlan.com. Happy Sheets-ing!
