Sum by weekday

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

This formatting is not required by the formula, but adding the day of week makes it easier to check results. You can read more about Excel's custom number formats here.

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.