The week numbers in column G are manually entered.
This problem can be solved with theSUMIFS functionand theWEEKNUM functionas explained below.
In the table shown, column E is ahelper columnwith week numbers generated with theWEEKNUM function.
The reference [@Date] means: current row in Date column.
If you are new to structured references, see this short video:Introduction to structured references.
The WEEKNUM function takes a validExcel dateas the first argument.
The second argument is calledreturn_typeand indicates the day of the week that week numbers should begin.
Settingreturn_typeto 2 specifies that week numbers should begin on Mondays.
TheSUMIFS functionis designed to sum values in rangesconditionallybased on multiple criteria.
The signature of the SUMIFS function looks like this:
Notice thesum_rangecomes first, followed byrange/criteriapairs.
Each range/criteria pair of arguments represents another condition.
This is because week numbers in Excel can vary in the early part of a year.
For example, the first few days of a year could be in week number 53.
At this point, we are ready to sum amounts by week number.
Inside the SUM function, this value is compared toweeks.
Sinceweekscontains all 12 values, the result is an array with 12 TRUE and FALSE results.
The TRUE and FALSE values are multiplied byamounts.
TheSUM functionthen sums the resulting array and returns the result.
The HSTACK function combinesuweeksandtotalshorizontally, and VSTACK combines the header row and the data to make the final table.
The final resultspillsinto multiple cells on the worksheet.
The WEEKNUM function starts counting on the week that contains January 1.
By default, weeks begin on Sunday, but this can be changed.
LET Function
The Excel LET function lets you define named variables in a formula.
BYROW can apply stock functions like SUM, COUNT, and AVERAGE or a custom LAMBDA function.
