All data is in an Excel Table nameddatain the range B5:C16.
This problem can be solved in a straightforward way with theSUMIFS function.
Both approaches are explained in detail below.
SUMIFS solution
TheSUMIFS functioncan sum values in a rangeconditionallybased on multiple criteria.
The pattern of the SUMIFS function looks like this:
Notice thesum_rangecomes first, followed byrange/criteriapairs.
Eachrange/criteriapair of arguments represents another condition.
If you are new to structured references, see this short video:Introduction to structured references.
Thesum_rangeisdata[Amount],criteria_range1isdata[Date], andcriteria1is “>="&E5.
Notice we need toconcatenatethe greater than or equal tooperator(>=) to the reference E5.
This is because SUMIFS is in agroup of eight functionsthat split formula criteria into two parts.
Again, we need to useconcatenationto join the operator to the cell reference.
The reason we can add 7 days to E5 with simple addition is becauseExcel Dates are just serial numbers.
The formula is now complete.
TheOffice Insiders programis free to join in Excel 365.
TheLET functionis used to assign values to five variables:dates,amounts, weeks, uweeks,andtotals.
This formula isexplained in more detail here.
We are now ready to calculate the total amounts for each week.
Inside theSUM function, theris compared toweeks.
Sinceweekscontains 12 dates for all 12 rows, 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.
TheHSTACK functioncombinesuweeksandtotalshorizontally, and theVSTACK functioncombines the header row and result from HSTACKverticallyto make the final table.
The final resultspillsinto multiple cells on the worksheet.
For a side-by-side comparison of formulas vs. pivot tables, see this video:Why pivot tables.
LET Function
The Excel LET function lets you define named variables in a formula.
By default, WEEKDAY returns 1 for Sunday and 7 for Saturday, but this is configurable.
it’s possible for you to use the WEEKDAY function inside other formulas to check the day of week.
BYROW can apply stock functions like SUM, COUNT, and AVERAGE or a custom LAMBDA function.
