Sum values if weekends
To sum all of the values that are on a weekend you can apply an Excel or a VBA method using a combination of Excel SUMPRODUCT and WEEKDAY functions
Example: Sum values if weekends
|
=SUMPRODUCT((WEEKDAY(B8:B14,2)>=C5)*D8:D14)
|
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 days of the weekend. The formula is summing all of the amounts that are related to Saturday and Sunday by using the >=C5 as the day of the week (C5 being 6). The 6 represents Saturday, therefore the formula sums all of the amounts that relate to Saturday and Sunday.
|
Dim ws As Worksheet
ws.Range("F8").Formula = "=SUMPRODUCT((WEEKDAY(B8:B14,2)>=C5)*D8:D14)"
End Sub
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.
Worksheet Name: Have a worksheet named Analyst.
Date Range: Ensure that the dates that you want to test are captured in range ("B8:B14") in the Analysis worksheet.
Sum Range: Ensure that the sum range (corresponding range to the date range) is captured in range ("D8:D14") in the Analysis worksheet.
Day of the weekend: Ensure that the first day of the weekend, in this example being a Saturday (value of 6) given we are summing only the weekends, is captured in cell ("C5") in the Analysis worksheet. The first 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 ("F8") 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 ("B8:B14") 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 ("D8:D14") to any range in the worksheet, that doesn't conflict with the formula.
Day of the weekend: Select the first day of the weekend, in this example being a Saturday (value of 6) given we are summing only the weekends, by changing the cell reference ("C5") to any range in the worksheet, that doesn't conflict with the formula. The first day of the weekend needs to be in the same format as the return type that has been selected in the WEEKDAY function.
To sum all of the values that are on a weekend you can apply an Excel or a VBA method. The formula used to sum all of the values that are on a weekend 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 days of the weekend. The formula is summing all of the amounts that are related to Saturday and Sunday by using the >=C5 as the day of the week (C5 being 6). The 6 represents Saturday, therefore the formula sums all of the amounts that relate to Saturday and Sunday.
=SUMPRODUCT((WEEKDAY(date_range,2)>=first_day_of_the_weekend)*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.
first_day_of_the_weekend: The first day of the weekend. In this example we are summing all of the weekends, including Saturday and Sunday, and therefore need to apply the greater than or equal to sing (>=) against the value that relates to Saturday (6), which corresponds to the return type (2).
sum_range: The range of cells you want to sum from.