Count cells if weekdays
This tutorial shows how to count all cells that have a weekday through the use of an Excel formula or VBA
Example: Count cells if weekdays
|
=SUMPRODUCT(--(WEEKDAY(B8:B14,2)<=C5))
|
This formula uses the Excel WEEKDAY function to convert a date to a day of the week based on the return type. The Excel SUMPRODUCT function is then used to count the number of times any of the weekdays (Monday - Friday) appears in the selected range. The formula is counting all of the occurrences of Monday through to Friday by using the <=C5 as the day of the week (C5 being 5). The 5 represents Friday, therefore the formula counts all of the occurrences from Friday through to Monday, as Monday is the first day of the week (based on the selected return type (2)).
|
Sub Count_cells_if_weekdays()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply the formula to count cells if weekdays
ws.Range("F8").Formula = "=SUMPRODUCT(--(WEEKDAY(B8:B14,2)<=C5))"
ws.Range("F8").Formula = "=SUMPRODUCT(--(WEEKDAY(B8:B14,2)<=C5))"
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("F8") in the VBA code.
Date and Count Range: Select the range of the dates that you want to count by changing range ("B8:B14") in the VBA code.
Day of the week: Select the last day of the week, in this example being a Friday (value of 5) given we are counting only the weekdays, by changing cell ("C5") in the VBA code. 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.
Worksheet Selection: Select the worksheet which captures a range of cells from which you want to count the number of dates that fall on a weekday by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Range: Select the output range by changing the cell reference ("F8") in the VBA code.
Date and Count Range: Select the range of the dates that you want to count by changing range ("B8:B14") in the VBA code.
Day of the week: Select the last day of the week, in this example being a Friday (value of 5) given we are counting only the weekdays, by changing cell ("C5") in the VBA code. 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.
Worksheet Selection: Select the worksheet which captures a range of cells from which you want to count the number of dates that fall on a weekday by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
METHOD 2. Count values if weekdays using VBA
VBA
Sub Count_cells_if_weekdays()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
counter = 0
'count cells if weekdays
For x = 8 To 14
For x = 8 To 14
If Weekday(ws.Range("B" & x), 2) <= ws.Range("C5") Then
counter = counter + 1
End If
Next x
ws.Range("F8") = counter
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("F8") in the VBA code.
Date and Count Range: The row and column references of this range are represented separately. The single column reference is represented by "B" and the row reference is represented by the values that are assigned to "x" which are from "8" to "14". These column and row references can be changed directly in the VBA code.
Day of the week: Select the last day of the week (Friday) by changing the cell reference ("C5") in the VBA code. 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 Selection: Select the worksheet which captures a range of cells from which you want to count the number of dates that fall on a weekday by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Range: Select the output range by changing the cell reference ("F8") in the VBA code.
Date and Count Range: The row and column references of this range are represented separately. The single column reference is represented by "B" and the row reference is represented by the values that are assigned to "x" which are from "8" to "14". These column and row references can be changed directly in the VBA code.
Day of the week: Select the last day of the week (Friday) by changing the cell reference ("C5") in the VBA code. 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 Selection: Select the worksheet which captures a range of cells from which you want to count the number of dates that fall on a weekday by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
EXPLANATION
This tutorial shows how to count all cells that are associated with a weekday using an Excel formula and VBA.
This tutorial provides one Excel method that can be applied to count the number of cells that capture a weekday in a selected range by using the Excel SUMPRODUCT and WEEKDAY functions. In this example, in the image above, we are counting the number of cells that capture a weekday (Monday to Friday) in range (B8:B14).
This tutorial provides two VBA methods that can be applied to count number of cells that capture a weekday in a selected range. The first method uses the VBA formula and the exact same formula that is provided in the Excel method. The second method uses the Weekday function to return the number of occurrences of weekdays (Monday to Friday) in a selected range. This is achieved by looping through each of the cells in the specified date range.
FORMULA
=SUMPRODUCT(--(WEEKDAY(date_count_range,2)<=day_of_the_week))
=SUMPRODUCT(--(WEEKDAY(date_count_range,2)<=day_of_the_week))
ARGUMENTS
date_count_range: The range of dates that you want to count.
day_of_the_week: The day of the week that you are trying to count. In this example we are counting all of the weekdays from Monday 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).
date_count_range: The range of dates that you want to count.
day_of_the_week: The day of the week that you are trying to count. In this example we are counting all of the weekdays from Monday 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).
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Count cells by weekdays | How to count cells by weekdays using Excel and VBA methods | |
| Count cells by weekends | How to count cells by weekends using Excel and VBA methods | |
| Count cells if weekends | How to count all cells that have a weekend using Excel and VBA methods | |
| Count cells with values in odd rows | How to count cells with values in odd rows using Excel and VBA methods | |
| Count cells with values in even rows | How to count cells with values in even rows using Excel and VBA methods |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| SUMPRODUCT Function | The Excel SUMPRODUCT function multiplies corresponding ranges and returns the sum of these values | |
| WEEKDAY Function | The Excel WEEKDAY function returns a number between 1 and 7, representing the day of the week based on the return type that has been selected |