Google Sheet Formula Help: Summarize weekly sales from daily sales data.

March 9, 2022 8:18 PM

I have a list of total sales made per day over a two year period, but need a formula that will summarize this on a "per week" basis. Help!?

Apologies in advance to the "what the hell is he talking about!?" camp... that's usually me!

I have three different tables set up:

1. "Raw Data": My raw sales dates, which line by line show each purchase by date, amount, and quantity. I will be updating this file regularly by copy and pasting my sales report. Column E has the sales date of the order, column J has the number of items purchased in the order.
2. "Daily Summary": My daily summary, which extracts data from the above and sums total sales, and total item sales, per day (formula for the total item sales: =SUMIF('Raw Data'!E:E,A2,'Raw Data'!J:J) ) ... column A in this tab is the date.
3. "Weekly Summary": Where I don't know what formula to us, to break down the daily summary into 7 day segments so I can see how many total items I sold on any given week.

Any formula wizards out there who can help?

posted by Unsomnambulist to Work & Money (7 answers total)

Best answer: I'm not a real expert (keep having to relearn some of this) but here's at least part of what I think you need

1. A column where the formula is =ISOWEEKNUM($A2) which gives you the week number from the date
2. A pivot table that summarizes by the week number
posted by TimHare at 8:28 PM on March 9, 2022 [3 favorites]

Best answer: TimHare is correct. Also, WEEKNUM gives you a little more customization as to what day of the week each week starts on (i.e. Sunday vs. Monday).
posted by HeroZero at 8:33 PM on March 9, 2022 [1 favorite]

Best answer: You shouldn't have your daily summary table constructed of sumifs, that should 100% be a pivot table. I know that you can then group dates into various units but I'm not at my computer and not sure if week is one of them. If so, just do that, if not, add that week column to your raw data.

Here's a page with pretty good instructions. Pivot tables are about to change your life.
posted by brainmouse at 8:46 PM on March 9, 2022 [3 favorites]

Response by poster: Thanks! Pivot table have indeed blown my mind. (Alas, disappointing not able to break down weekly there. And I want to avoid needing to edit my raw data, or add to it... defeats the purpose.)
And WEEKNUM is very cool and puts me in the right direction, though it doesn't differentiate by year, so my sales from select weeks in 2020, 2021, and 2022 are all being combined (but I'll be able to figure this out).
Great input all, and definitely learned a ton more about formulas, even if I haven't solved this quandry quite yet...
posted by Unsomnambulist at 9:53 PM on March 9, 2022

Meh, I think pivots are overrated, particularly if you want to use the resultant data somewhere else.

You can apply the same sumif process to your weekly totals that you do to your daily ones, with your date column being the week commencing date. Use SUMIFS instead, for multiple conditions, and set it to sum based on the date of the translation is within 7 days of your week commencing date, i.e. =SUMIFS('Raw Data'!E:E,Raw Data'!J:J, "<>"&A1)
posted by Faff at 10:22 PM on March 9, 2022

Don't be afraid to add columns! You can do Weeknum, then concatenate the week number and year number to get for example 2020 week 10, then you can either pivot-table or sumif by that column.

(Both sumif and pivot tables are equally valid for various uses. Pivot tables don't automatically refresh, so if you plan to add to the data or work on it, they're not an elastic solution.)
posted by I claim sanctuary at 11:41 PM on March 9, 2022

Pivot tables don't automatically refresh

Fwiw, pivot tables do automatically refresh in Google Sheets, this is just an Excel problem.
posted by brainmouse at 8:05 AM on March 10, 2022