Explanation
In this example, the goal is to sum the amounts in column D by week number, using the dates in column C to determine the week number. The week numbers in column G are manually entered. The final results should appear in column H. All data is in an Excel Table named data in the range B5:E16. This problem can be solved with the SUMIFS function and the WEEKNUM function as explained below.
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:
=WEEKNUM([@Date],2) // get week number
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:
=SUMIFS(sum_range,range1,criteria1,range2,criteria2,...)
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 :
=SUMIFS(data[Amount]
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:
=SUMIFS(data[Amount],data[Week],G5)
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:
=LET(
weeks,WEEKNUM(+data[Date],2),
amounts,data[Amount],
uweeks,UNIQUE(weeks),
totals, BYROW(uweeks, LAMBDA(r, SUM((weeks=r)*amounts))),
VSTACK({"Week","Total"},HSTACK(uweeks, totals))
)
The LET function is used to assign four intermediate variables: weeks , uweeks, totals , and totals . The value for weeks is created like this:
WEEKNUM(+data[Date],2) // get all weeks
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:
{1;1;2;2;3;3;4;5;5;6;6;6}
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:
amounts,data[Amount]
Note: Technically, we could just use the reference to data[Amount], but defining a variable here keeps all 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:
UNIQUE(weeks) // get unique week numbers
From the 12 week numbers seen above, the UNIQUE function returns just 6 unique numbers:
{1;2;3;4;5;6} // unique week 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 totals for each week, like this:
totals, BYROW(uweeks, LAMBDA(r, SUM((weeks=r)*amounts)))
BYROW runs through the uweeks values row by row. At each row, it applies this calculation:
LAMBDA(r, SUM((weeks=r)*amounts))
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:
{204;202;230;165;280;450} // totals
This is the value assigned to totals .
Finally, the HSTACK and VSTACK functions are used to assemble a complete table:
VSTACK({"Week","Total"},HSTACK(uweeks, totals))
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.
Explanation
In this example, the goal is to sum amounts by weekday. In other words, we want to sum amounts by Monday, Tuesday, Wednesday, and so on. Column B contains valid Excel dates formatted with a custom number format explained below. For convenience, all source data is in an Excel Table named data . The values in E5:E11 are hardcoded text values . A nice way to solve this problem is to use the SUMPRODUCT function together with the TEXT function . The SUMIFS function is not a good fit here for reasons explained below.
Why not SUMIFS?
You might wonder why we aren’t using the SUMIFS function to solve this problem? The reason is that SUMIFS is in a group of eight functions that requires a range for the criteria_range argument; it is not possible to provide an array instead. This means we can’t extract a day of week value from the date in column B and feed that into SUMIFS as a range argument, unless we add a helper column to the source data.
Custom number format
The dates in column B are formatted with a custom number format to show an abbreviated day of week at the start:
ddd dd-mmm-yy
SUMPRODUCT with TEXT
In the worksheet shown, the solution is based on the SUMPRODUCT function together with the TEXT function. The formula in cell F5 is:
=SUMPRODUCT((TEXT(data[Date],"ddd")=E5)*(data[Amount]))
Working from the inside out, the TEXT function is used to extract a 3-letter abbreviation for each day of the week like this:
TEXT(data[Date],"ddd")
Because data[Date] contains 12 dates, the TEXT function returns 12 values in an array like this:
{"Thu";"Sat";"Wed";"Mon";"Tue";"Tue";"Thu";"Tue";"Sun";"Fri";"Wed";"Mon"}
This array is compared against the value in cell E5 (“Mon”) which results in an array of 12 TRUE and FALSE values:
{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}
In this array, TRUE represents dates that are Monday, and FALSE represents dates that are other days of the week. In the next step, the array above is multiplied by data[Amount] , which contains numeric values. This math operation coerces the TRUE and FALSE values to 1s and 0s, so we can visualize the operation like this:
={0;0;0;1;0;0;0;0;0;0;0;1}*(data[Amount])
={0;0;0;275;0;0;0;0;0;0;0;150}
Essentially, the array from the TEXT function acts like a filter: only amounts that correspond to 1 survive the operation, the rest of the amounts are “zeroed out”. All of this happens inside the SUMPRODUCT function, which receives the final array:
=SUMPRODUCT({0;0;0;275;0;0;0;0;0;0;0;150}) // returns 425
With just one array to process, the SUMPRODUCT function sums the array and returns the final result, 425. As the formula is copied down the column, we get a subtotal for each day listed in E5:E11.
Note: the reason we use the SUMPRODUCT function in this formula and not the SUM function is that SUMPRODUCT will work in all versions of Excel without special handling. In the current version of Excel , the SUM function will also work as a replacement for SUMPRODUCT. In Legacy Excel , the SUM function must be entered as an array formula with control + shift + enter.
SUMPRODUCT with WEEKDAY
Another way to approach this problem is to use the SUMPRODUCT function with the WEEKDAY function like this:
=SUMPRODUCT((WEEKDAY(dates)=day_num)*values)
The WEEKDAY function returns a numeric value for each day of the week. The challenge with this approach is that it’s more cryptic: you have to know what number corresponds to each day of the week. This page provides details on how WEEKDAY operates.
