Sum values by weekends
To sum values by weekends you can apply an Excel or a VBA method using a combination of Excel SUMPRODUCT and WEEKDAY functions
Example: Sum values by weekends
|
=SUMPRODUCT((WEEKDAY(B5:B11,2)=C14)*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 weekend. The formula is summing all of the amounts that are related to Saturday which carries a value of 6 as the day of the weekend. This can be reproduced for Sunday by changing the day of the weekend (No. Format) to 7, as per the above example.
|
Sub Sum_values_by_weekends()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply the formula to sum values by weekends
ws.Range("D14").Formula = "=SUMPRODUCT((WEEKDAY(B5:B11,2)=C14)*D5:D11)"
ws.Range("D14").Formula = "=SUMPRODUCT((WEEKDAY(B5:B11,2)=C14)*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 weekend: Ensure that the day of the weekend is captured in cell ("C14") in the Analysis worksheet. The day of the weekend 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 weekend: Ensure that the day of the weekend is captured in cell ("C14") in the Analysis worksheet. The day of the weekend 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 ("D14") 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 weekend: Select the day of the weekend by changing the cell reference ("C14") to any range in the worksheet, that doesn't conflict with the formula. The day of the weekend 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 ("D14") 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 weekend: Select the day of the weekend by changing the cell reference ("C14") to any range in the worksheet, that doesn't conflict with the formula. The day of the weekend needs to be in the same format as the return type that has been selected in the WEEKDAY function.
EXPLANATION
To sum values by weekends you can apply an Excel or a VBA method. The formula used to sum values by weekends 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 weekend 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 weekend. The formula is summing all of the amounts that are related to Saturday which carries a value of 6 as the day of the weekend. This can be reproduced for Sunday by changing the day of the weekend (No. Format) to 7, as per the above example.
To sum values by weekends you can apply an Excel or a VBA method. The formula used to sum values by weekends 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 weekend 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 weekend. The formula is summing all of the amounts that are related to Saturday which carries a value of 6 as the day of the weekend. This can be reproduced for Sunday by changing the day of the weekend (No. Format) to 7, as per the above example.
FORMULA
=SUMPRODUCT((WEEKDAY(date_range,2)=day_of_the_weekend)*sum_range)
=SUMPRODUCT((WEEKDAY(date_range,2)=day_of_the_weekend)*sum_range)
ARGUMENTS
date_range: The range of dates for which you want to find the day of the weekend and corresponds to the sum range.
day_of_the_weekend: The day of the weekend that you are trying to sum. In the example above we are summing by individual weekends, therefore, we use numbers 6 to 7, which corresponds to the return type that we selected (2).
sum_range: The range of cells you want to sum from.