where data is an Excel Table in the range B5:C16. As the formula is copied down, the formula returns a sum for each day of the week in column E.
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: 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: Working from the inside out, the TEXT function is used to extract a 3-letter abbreviation for each day of the week like this: Because data[Date] contains 12 dates, the TEXT function returns 12 values in an array like this: This array is compared against the value in cell E5 (“Mon”) which results in an array of 12 TRUE and FALSE values: 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: 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: 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: 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.
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.
