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 validExcel datesformatted with a custom number format explained below.
For convenience, all source data is in anExcel Tablenameddata.
The values in E5:E11 are hardcodedtext values.
A nice way to solve this problem is to use theSUMPRODUCT functiontogether with theTEXT 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 theSUMIFS functionto solve this problem?
it’s possible for you to read more about Excel’s custom number formatshere.
In the next step, the array above is multiplied bydata[Amount], which contains numeric values.
In thecurrent version of Excel, the SUM function will also work as a replacement for SUMPRODUCT.
InLegacy Excel, the SUM function must be entered as anarray formulawith control + shift + enter.
in a text string with the number format of your choice.
By default, WEEKDAY returns 1 for Sunday and 7 for Saturday, but this is configurable.
it’s possible for you to use the WEEKDAY function inside other formulas to check the day of week.
