where data is an Excel Table in the range B5:E16, and the week numbers in column E are generated with the WEEKNUM function.
WEEKNUM function
The first step in this problem is to generate a week number for each date in column B. In the table shown, column E is a helper column with week numbers generated with the WEEKNUM function. The formula in E5, copied down, is: Note: because we are using an Excel Table to hold the data, we automatically get the structured reference seen above. 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 valid Excel date as the first argument. The second argument is called return_type and indicates the day of the week that week numbers should begin. Setting return_type to 2 specifies that week numbers should begin on Mondays.
SUMIFS solution
The next step in the problem is to add up the amounts in column D using the week numbers in column E. This can easily be done with the SUMIFS function. The SUMIFS function is designed to sum values in ranges conditionally based on multiple criteria. The signature of the SUMIFS function looks like this: Notice the sum_range comes first, followed by range/criteria pairs. Each range/criteria pair of arguments represents another condition. In this case, we need to configure SUMIFS to sum values by week number using just one condition: we need to check the week number in column E for a match in the week number in column G. We start off with the sum_range: Next, we add criteria as a range/criteria pair, where criteria_range1 is the date column, and criteria1 is the week number in column G: Note: we don’t use data[Week]=G5 because SUMIFS is in a group of eight functions that split formula criteria into two parts. As the formula is copied down, we get a total for each week number in column G.
Dynamic array solution
In the latest version of Excel, which supports dynamic array formulas, it is possible to create a single all-in-one formula that builds the entire summary table, including headers, like this: The LET function is used to assign four intermediate variables: weeks, uweeks, totals, and counts. The value for weeks is created like this: Here, the WEEKNUM function is used to fetch week numbers for all dates in data[Date]. Because the table contains 12 rows, the result is an array with 12 week numbers like this: Note: the + operator before data[Date] is a workaround for some Excel functions that don’t spill properly. In the next line, we assign a value to amounts like this: Note: technically, we could just use the reference to data[Amount], but defining a variable here keeps all of the worksheet references at the top of the formula where they can be easily changed. In our summary table, we want a list of unique week numbers, so we define uweeks (unique weeks) with the UNIQUE function like this: From the 12 week number values seen above, the UNIQUE function returns just 6 unique numbers: Note: you could run these week numbers through the SORT function to ensure that weeks are in the correct order, but a better approach would be to SORT the dates themselves before extracting the week numbers. 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. We do this with the BYROW function which calculates the sums and assigns the result to the variable counts for each week like this: BYROW runs through the uweeks values row by row. At each row, it applies this calculation: The value for r is the week number in the “current” row. Inside the SUM function, this value is compared to weeks. Since weeks contains all 12 values, the result is an array with 12 TRUE and FALSE results. The TRUE and FALSE values are multiplied by amounts. The math operation automatically converts the TRUE and FALSE values to 1s and 0s, and the zeros effectively “cancel” the values in week numbers not equal to r. The SUM function then sums the resulting array and returns the result. When BYROW is finished, we have an array with six sums like this: This is the value assigned to totals. Finally the HSTACK and VSTACK functions are used to assemble a complete table: At the top of the table, the array constant {“Week”,“Total”} creates a header row. The HSTACK function combines uweeks and totals horizontally, and VSTACK combines the header row and the data to make the final table. The final result spills into multiple cells on the worksheet.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
