Sum by week

In this example, the goal is to sum the amounts in column C by week, using the dates in the range E5:E10 which are all Mondays. All data is in an Excel Table named data in the range B5:C16. This problem can be solved in a straightforward way with the SUMIFS function. In the current version of Excel, which supports dynamic array formulas, you can also create an all-in-one formula that builds the entire summary table in one step. Both approaches are explained in detail below.

SUMIFS solution

The SUMIFS function can sum values in a range conditionally based on multiple criteria. The pattern 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 using two criteria: one to match dates greater than or equal to the first day of the week, one to match dates less than the first day of the next week. We start off with the sum_range and the first condition:

=SUMIFS(data[Amount],data[Date],">="&E5

Note: because we are using an Excel Table to hold the data, we automatically get the structured references seen above. If you are new to structured references, see this short video: Introduction to structured references.

The sum_range is data[Amount], criteria_range1 is data[Date], and criteria1 is ">="&E5. Notice we need to concatenate the greater than or equal to operator (>=) to the reference E5. This is because SUMIFS is in a group of eight functions that split formula criteria into two parts.

Next, we need to add a second range/criteria pair of arguments to target dates through the last day of the week:

=SUMIFS(data[Amount],data[Date],">="&E5,data[Date],"<"&E5+7)

Here, criteria_range2 is again data[Date], and criteria2 is "<"&E5+7. Basically, we add 7 days to the date in E5 and use the less than operator (<) to catch all days prior to the next week. Again, we need to use concatenation to join the operator to the cell reference. The reason we can add 7 days to E5 with simple addition is because Excel Dates are just serial numbers.

The formula is now complete. As the formula is copied down column F, the SUMIFS formula will generate a sum for each week using the date in column F.

Week of dates

The dates in column E are Mondays. The first date in E5 (3-Jan-22) is hard-coded, and the rest of the dates are calculated with a simple formula:

=E5+7

At each new row, the formula returns the next Monday in the calendar.

Dynamic array solution

In the current version of Excel, which supports dynamic array formulas, it is possible to create a single all-in-one formula that builds out the entire summary table, including headers, in one step like this:

=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)))

Note: Currently, VSTACK and HSTACK are only available through the Beta channel of Office Insiders. The Office Insiders program is free to join in Excel 365. 

The LET function is used to assign values to five variables: dates, amounts, weeks, uweeks, and totals. First, we assign values to dates and amounts like this:

=LET(
dates,data[Date],
amounts,data[Amount]

Technically, we could just use the references data[date] and data[Amount] throughout the formula, but defining these variables for these up front keeps all worksheet references at the top of the code where they can be easily changed. In other words, by editing just these two references, you can easily adapt the formula to work with a different data set.

Next, the value for weeks is created like this:

weeks,dates-WEEKDAY(dates,3)

Here the WEEKDAY function is used to calculate a "Monday of the week" for each date in data[Date]. This formula is explained in more detail here. Because the table contains 12 rows of data, the result is an array with 12 dates like this:

{44564;44564;44571;44578;44578;44578;44585;44592;44592;44592;44599;44599}

Excel dates are just large serial numbers, so these are the raw numbers that correspond to the dates seen in E5:E10, which are all Mondays.

In the next line we define uweeks (unique weeks) with the UNIQUE function like this:

uweeks,UNIQUE(weeks)

We do this because we only want one row per week in our final summary table. The UNIQUE function returns the 6 dates seen in E5:E10, which are all Mondays:

{44564;44571;44578;44585;44592;44599}

Note: you could sort the result from UNIQUE with the SORT function to ensure that weeks are in the correct order if needed.

We are now ready to calculate the total amounts for each week. We do this with the BYROW function which generates the sums and assigns the result to the variable totals 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 with the LAMBDA function:

LAMBDA(r,SUM((weeks=r)*amounts))

The value of r is the date in the "current" row of uweeks. Inside the SUM function, the r is compared to weeks. Since weeks contains 12 dates for all 12 rows, the result is an array with 12 TRUE and FALSE results. The TRUE and FALSE values are multiplied by amounts. This math operation automatically converts the TRUE and FALSE values to 1s and 0s, and the zeros effectively "cancel out" the values in weeks not equal to r. The SUM function then sums the resulting array. When BYROW is finished, we have an array with 6 weekly sums like this:

{225;80;150;90;350;235} // totals

This is the value assigned to the variable totals.

Finally the HSTACK and VSTACK functions are used to assemble a complete table:

VSTACK({"Week of","Total"},HSTACK(uweeks,totals))

At the top of the table, the array constant {"Week of","Total"} creates a header row. The HSTACK function combines uweeks and totals horizontally, and the VSTACK function combines the header row and result from HSTACK vertically to make the final table. The final result spills into multiple cells on the worksheet.

Pivot Table solution

A pivot table is an excellent solution when you need to summarize data by year, month, quarter, and so on. For a side-by-side comparison of formulas vs. pivot tables, see this video: Why pivot tables.