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.
Explanation
In this example, the goal is to calculate a total for each year that appears in column F. All data is in an Excel Table called data in the range B5:D16. The main challenge is that we have dates in column B, but we don’t have separate year values to work with. The simplest way to solve this problem is with the SUMPRODUCT function using the formula seen in the worksheet above. The problem can also be solved with the SUMIFS function , or with a dynamic array formula . All three approaches are explained below.
SUMPRODUCT function
In the example shown, the formula in cell G5 is:
=SUMPRODUCT((YEAR(data[Date])=F5)*data[Amount])
Working from the inside out, the first step is to extract year values from the dates in column B, which is done with the YEAR function:
YEAR(data[Date]) // get years
Because there are 12 dates in the column, YEAR returns 12 results in an array like this:
{2020;2020;2021;2022;2020;2021;2021;2022;2020;2021;2022;2022}
Each year in this array corresponds to a date in data[Date] . Next, the year values are compared to the year in cell F5:
YEAR(data[Date])=F5
The result is an array of 12 TRUE and FALSE values like this:
{TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}
Notice TRUE values correspond to dates that occur in 2020, the value in F5. Next, this array is multiplied by the amounts in column D. The math operation automatically converts the TRUE and FALSE values to 1s and 0s, so we can visualize this operation like this:
{1;1;0;0;1;0;0;0;1;0;0;0}*data[Amount]
Remember the 1s in the first array correspond to dates that occur in 2020, while 0s indicate dates in other years. The zeros in the first array effectively “cancel out” amounts in other years. The resulting array is returned to SUMPRODUCT like this:
=SUMPRODUCT({1500;1250;0;0;1850;0;0;0;1250;0;0;0})
With only one array to process, SUMPRODUCT sums the array and returns a final result of 5850. As the formula is copied down, the cell reference F5, which is relative, changes at each new row, and we get the correct total for each year shown.
SUMIFS function
Another way to solve this problem is with the SUMIFS function and a formula like this:
=SUMIFS(data[Amount],data[Date],">="&DATE(F5,1,1),data[Date],"<="&DATE(F5,12,31))
If you are new to SUMIFS, this article covers the basics. This formula is more complicated than the SUMPRODUCT option because SUMIFS has no way to get at the year values directly. This limitation is discussed in more detail here . Instead, we need to create two dates for each year with the DATE function , then test for dates between these two dates:
DATE(F5,1,1) // start "Jan 1, 2020"
DATE(F5,12,31) // end "Dec 31,2020"
For the start date, we simply hardcode 1 for month and day arguments to get a January 1 date. For the end date, we hardcode 12 for month and 31 for day to create a December 31 date. For both dates, year comes from cell F5, which contains 2020. In summary, we use the DATE function to create a first-of-year date and a last-of-year date based on the year value in column F.
We also need logical operators to “bracket” each year. We concatenate the operators like this:
">="&DATE(F5,1,1) // start date
"<="&DATE(F5,12,31) // end date
The requirement to concatenate operators like this is again a “feature” of the SUMIFS function, which shares this peculiar syntax with eight other functions .
After the DATE function is evaluated, and after concatenation is complete, we have:
=SUMIFS(data[Amount],data[Date],">=43831",data[Date],"<=44196")
The number 43831 is the Excel serial number date for January 1, 2020. The number 44196 is the serial number for December 31, 2020.
Note: with either of the two options above, you could use the UNIQUE function to fetch the unique years from the dates as an initial step, if you have a modern version of Excel .
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 the entire summary table, including headers, like this:
=LET(
years,YEAR(data[Date]),
values,data[Amount],
uyears,SORT(UNIQUE(years)),
sums, BYROW(uyears, LAMBDA(r, SUM(--(years=r)*values))),
VSTACK({"Year","Total"},HSTACK(uyears, sums))
)
Because this formula is doing a lot more work, it is more complicated. At the highest level, the LET function is used to assign values to four variables: years , values , uyears , and sums . First, we assign values to years and values like this:
=LET(
years,YEAR(data[Date]),
values,data[Amount],
Technically, we could just use the references for data[Date] and data[Amount] throughout the formula, but defining these variables up front makes the formula more portable. With a different set of data, only these two references need to be changed and the formula will continue to work.
The value for years is created with the YEAR function like this:
years,YEAR(data[Date]) // extract years
YEAR extracts just the year from all dates in data[Date] . Because the table contains 12 rows, the result is an array with 12 year values like this:
{2020;2020;2021;2022;2020;2021;2021;2022;2020;2021;2022;2022}
Next, values is assigned like this:
values,data[Amount]
As mentioned above, we do this to avoid repeating the reference elsewhere in the formula. In the following line, the value for uyears (unique years) is created like this:
SORT(UNIQUE(years)) // get and sort unique years
From the12 year values, the UNIQUE function returns just 3 unique years:
{2020;2021;2022} // unique
This array is returned to the SORT function, which sorts the array in ascending order:
{2020;2021;2022} // sorted
Note: In this example, it happens that the unique years are already in ascending order, so the SORT function does not change the result from UNIQUE. However, using the SORT function ensures that year values will always appear in order when source data is not sorted.
Next, the BYROW function is used to create a value for sums for each year like this:
sums,BYROW(uyears,LAMBDA(r,SUM(--(years=r)*values))) // sums
This is the core calculation in the formula. BYROW runs through the uyears values row by row. At each row, it applies this calculation:
LAMBDA(r,SUM(--(years=r)*values))
The value for r is the year in the “current” row. Inside the SUM function , this value is compared to years . Since years contains all 12 years, the result is an array with 12 TRUE and FALSE results. The TRUE and FALSE values are converted to 1s and 0s with the double negative (–), and the resulting array is multiplied by values . The 0s in the first array effectively “cancel out” amounts not associated with the year of interest.
Note: technically, the double negative (–) isn’t needed here, because the math operation of multiplication automatically converts the TRUE and FALSE values to 1s and 0s. However, the double negative does no harm, and arguably makes the formula easier to read by clearly signaling an operation that cancels out values.
Next, the SUM function then sums the resulting array, which represents the total sum of values in the current year. Since there are 3 unique years, the result from BYROW is an array with 3 sums like this:
{5850;7950;6500} // sums
Finally, the HSTACK and VSTACK functions are used to assemble a complete table:
VSTACK({"Year","Total"},HSTACK(uyears, sums))
At the top of the table, the array constant {“Year”,“Total”} creates a header row. The HSTACK function combines uyears and sums horizontally, and the VSTACK function combines the header row and the data to make the final table. The result spills into multiple cells on the worksheet:
