Sum values if weekdays
To sum all of the values that are on a weekday you can apply an Excel or a VBA method using a combination of Excel SUMPRODUCT and WEEKDAY functions
Example: Sum values if weekdays
|
=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 week. The formula is summing all of the amounts that are related to Monday through to Friday by using the <=C5 as the day of the week (C5 being 5). The 5 represents Friday, therefore the formula sums all of the amounts from Friday through to Monday, as Monday is the first day of the week (based on the selected return type (2)).
|
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 week: Ensure that the last day of the week, in this example being a Friday (value of 5) given we are summing only the weekdays, is captured in cell ("C5") in the Analysis worksheet. The last 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 ("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 week: Select the last day of the week, in this example being a Friday (value of 5) given we are summing only the weekdays, by changing the cell reference ("C5") to any cell in the worksheet, that doesn't conflict with the formula. The last day of the week 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 weekday you can apply an Excel or a VBA method. The formula used to sum all of the values that are on a weekday 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 week. In this example the formula is summing all of the amounts that are related to Monday through to Friday by using the <=C5 as the day of the week (C5 being 5). The 5 represents Friday, therefore the formula sums all of the amounts from Friday through to Monday.
=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 all of the weekdays from Monday through to Friday and therefore need to apply the less than or equal to sing (<=) against the value that relates to Friday (5), which corresponds to the return type (2).
sum_range: The range of cells you want to sum from.