![]() ![]() 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,"ddd")=E5)*(data)) ![]() You can read more about Excel's custom number formats here. This formatting is not required by the formula, but adding the day of week makes it easier to check results. 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 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. The SUMIFS function is not a good fit here for reasons explained below. A nice way to solve this problem is to use the SUMPRODUCT function together with the TEXT function. The values in E5:E11 are hardcoded text values. For convenience, all source data is in an Excel Table named data. Column B contains valid Excel dates formatted with a custom number format explained below. ![]() In other words, we want to sum amounts by Monday, Tuesday, Wednesday, and so on. In this example, the goal is to sum amounts by weekday. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |