Hi - Dave here.
I recently explained how to
sum by month with a formula.
This week, I want to explain how to
sum by week with a formula. This turns out to be a pretty straightforward problem because dates in Excel are large serial numbers, so you can just add 7 to get a date one week later.
In the worksheet below, you can see how this works with the SUMIFS function. The formula in cell F5, copied down, is:
=SUMIFS(data[Amount],data[Date],">="&E5,data[Date],"<"&E5+7)
[
download the workbook and read the full explanation]
The article above provides a detailed explanation and includes the Excel workbook. I've also included a dynamic array formula that builds the
entire summary table in one step with the LET, LAMBDA, and BYROW functions:
=LET(
dates,data[Date],
amounts,data[Amount],
weeks,dates-WEEKDAY(dates,3),
uweeks,UNIQUE(weeks),
totals,BYROW(uweeks,LAMBDA(r,SUM((weeks=r)*amounts))),
VSTACK({"Week of","Total"},HSTACK(uweeks,totals)))
You've probably never seen a formula like this. The result is
all values in E4:F10. These new functions are truly next-level and are going to radically change how challenging problems are solved in Excel. Download the workbook and see for yourself.
Excel formulas
We maintain a large list of working formulas
here.
If you need more structure, we also offer
video training.
Have a nice weekend!
Dave
