Sum values by weekdays
To sum values by weekdays you can apply an Excel or a VBA method using a combination of Excel SUMPRODUCT and WEEKDAY functions
Example: Sum values by weekdays
|
=SUMPRODUCT((WEEKDAY(B5:B11,2)=C16)*D5:D11)
|
The Excel WEEKDAY function converts the date to a day of the week based on the return type. The Excel SUMPRODUCT function is then used to sum all of the values that correspond to the specified day of the week. The formula is summing all of the amounts that are related to Wednesday which carries a value of 3 as the day of the week. This can be reproduced for any weekday by changing the day of the week (No. Format), as per the above example.
|
Sub Sum_values_by_weekdays()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply the formula to sum values by weekdays
ws.Range("D16").Formula = "=SUMPRODUCT((WEEKDAY(B5:B11,2)=C16)*D5:D11)"
ws.Range("D16").Formula = "=SUMPRODUCT((WEEKDAY(B5:B11,2)=C16)*D5:D11)"
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named Analyst.
Date Range: Ensure that the dates that you want to test are captured in range ("B5:B11") in the Analysis worksheet.
Sum Range: Ensure that the sum range (corresponding range to the date range) is captured in range ("D5:D11") in the Analysis worksheet.
Day of the week: Ensure that the day of the week is captured in range ("C16") in the Analysis worksheet. The day of the week needs to be in the same format as the return type that has been selected in the WEEKDAY function.
Worksheet Name: Have a worksheet named Analyst.
Date Range: Ensure that the dates that you want to test are captured in range ("B5:B11") in the Analysis worksheet.
Sum Range: Ensure that the sum range (corresponding range to the date range) is captured in range ("D5:D11") in the Analysis worksheet.
Day of the week: Ensure that the day of the week is captured in range ("C16") in the Analysis worksheet. The day of the week needs to be in the same format as the return type that has been selected in the WEEKDAY function.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("D16") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Date Range: Select the range were the dates are stored by changing the range ("B5:B11") to any range in the worksheet, that doesn't conflict with the formula.
Sum Range: Select the sum range (corresponding range to the date range) by changing the range ("D5:D11") to any range in the worksheet, that doesn't conflict with the formula.
Day of the week: Select the day of the week by changing the cell reference ("C16") to any cell in the worksheet, that doesn't conflict with the formula. The day of the week needs to be in the same format as the return type that has been selected in the WEEKDAY function.
Output Range: Select the output range by changing the cell reference ("D16") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Date Range: Select the range were the dates are stored by changing the range ("B5:B11") to any range in the worksheet, that doesn't conflict with the formula.
Sum Range: Select the sum range (corresponding range to the date range) by changing the range ("D5:D11") to any range in the worksheet, that doesn't conflict with the formula.
Day of the week: Select the day of the week by changing the cell reference ("C16") to any cell in the worksheet, that doesn't conflict with the formula. The day of the week needs to be in the same format as the return type that has been selected in the WEEKDAY function.
EXPLANATION
To sum values by weekdays you can apply an Excel or a VBA method. The formula used to sum values by weekdays is driven by a combination of Excel SUMPRODUCT and WEEKDAY functions.
In this example the Excel WEEKDAY function converts the date to a day of the week based on the return type. The Excel SUMPRODUCT function is then used to sum all of the values that correspond to the specified day of the week. This can be reproduced for any weekday by changing the day of the week (No. Format), as per the above example.
To sum values by weekdays you can apply an Excel or a VBA method. The formula used to sum values by weekdays is driven by a combination of Excel SUMPRODUCT and WEEKDAY functions.
In this example the Excel WEEKDAY function converts the date to a day of the week based on the return type. The Excel SUMPRODUCT function is then used to sum all of the values that correspond to the specified day of the week. This can be reproduced for any weekday by changing the day of the week (No. Format), as per the above example.
FORMULA
=SUMPRODUCT((WEEKDAY(date_range,2)=day_of_the_week)*sum_range)
=SUMPRODUCT((WEEKDAY(date_range,2)=day_of_the_week)*sum_range)
ARGUMENTS
date_range: The range of dates for which you want to find the day of the week and corresponds to the sum range.
day_of_the_week: The day of the week that you are trying to sum. In this example we are summing by individual weekday, therefore, we use numbers 1 to 5, which corresponds to the return type that we selected (2).
sum_range: The range of cells you want to sum from.