Sum by weekday

The Excel SUMPRODUCT is a very useful function which multiplies arrays or ranges together and then returns the sum of those products. You can use other functions inside SUMPRODUCT to further extend its functionality. Similarly, it can be used to sum the weekdays in a given range in Excel.  

Formula

= SUMPRODUCT ((WEEKDAY(dates_range) = day_number) * Values)

Explanation

The formula above is incorporating the SUMPRODUCT function to sum the number of each of the weekdays and uses the following arguments:

  • dates_range (required): It is the range of dates for the respective weekdays.
  • day_number (required): It is the respective number of the day for which we need to calculate the values.
  • Values (required): This is the range of values that need to be added for a respective day.

This works as follows:

  • The WEEKDAY function is set with the optional argument 2, which leads it to give numbers ranging between 1 to 7 for the days Monday to Sunday, respectively.
  • The WEEKDAY evaluates every value in the dates’ range and returns a number. The values returned are then compared with the day_number.
  • Then the array is multiplied by the values’ range. Finally, the SUMPRODUCT function adds the results and returns the final outcome.

Example 1

To sum by weekday in this example, the following formula is used using Excel SUMPRODUCT function;

=SUMPRODUCT((WEEKDAY(B5:B12,2)=H5)*D5:D12)

This works as follows:

  • The WEEKDAY function is set with the optional argument 2, which leads it to give numbers ranging between 1 to 7 for the days Monday to Sunday, respectively.
  • The WEEKDAY evaluates every value in the dates’ range and returns a number. The result is an array which looks like this {1;2;3;4;5;1;2;3}. The values returned are then compared with 1 (day_number). This results in an array of TRUE or FALSE values; {TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}
  • The TRUE and FALSE values are automatically converted into 1’s and 0’s. These are multiplied by the Amount values which leads to this result, {80;0;0;0;0;100;0;0}. Then, finally the SUMPRODUCT function sums the array values and prints the result.

Figure 1. Example of SUMPRODUCT Function to sum by weekday

Notes

Here we have not used the SUMIF or SUMIFS functions because without a helper column it is not possible to create criteria for SUMIF function that will take into account the weekdays. On the other hand, the SUMPRODUCT function can handle arrays very well without the need to use Control + Shift + Enter.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar