Count cells by weekdays
This tutorial shows how to count cells by weekdays through the use of an Excel formula or VBA
Example: Count cells by weekdays
|
=SUMPRODUCT(--(WEEKDAY(B5:B11,2)=C16))
|
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 the specified day of the week occurs. The formula counts the number of times Wednesday (referenced to cell "C16") is presented in the selected date range, which in this example is twice. This can be reproduced for any weekday by changing the day of the week (No. Format), as per the above example.
|
Sub Count_cells_by_weekdays()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'count cells by weekdays
ws.Range("D16").Formula = "=SUMPRODUCT(--(WEEKDAY(B5:B11,2)=C16))"
ws.Range("D16").Formula = "=SUMPRODUCT(--(WEEKDAY(B5:B11,2)=C16))"
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("D16") in the VBA code.
Date and Count Range: Select the range of the dates that you want to count by changing range ("B5:B11") in the VBA code.
Day of the week: Select the day of the week by changing the cell reference ("C16") to any cell that represents the day of the week that you want to count for. 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 for weekdays 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 ("D16") in the VBA code.
Date and Count Range: Select the range of the dates that you want to count by changing range ("B5:B11") in the VBA code.
Day of the week: Select the day of the week by changing the cell reference ("C16") to any cell that represents the day of the week that you want to count for. 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 for weekdays 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.
ADDITIONAL NOTES
Note 1: This VBA example only focuses on the result in cell ("D16"), which counts the number of dates that fall on Wednesday. However, the image that is illustrated in this tutorial applies across multiple cells.
Note 1: This VBA example only focuses on the result in cell ("D16"), which counts the number of dates that fall on Wednesday. However, the image that is illustrated in this tutorial applies across multiple cells.
METHOD 2. Count cells by weekdays for a single weekday using VBA
VBA
Sub Count_cells_by_weekdays()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
counter = 0
'count cells with the date that falls on a Wednesday
For x = 5 To 11
For x = 5 To 11
If Weekday(ws.Range("B" & x), 2) = ws.Range("C16") Then
counter = counter + 1
End If
Next x
ws.Range("D16") = counter
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("D16") in the VBA code.
Date and Count Range: This column and row 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 "5" to "11". These column and row references can be changed directly in the VBA code.
Day of the week: Select the day of the week by changing the cell reference ("C16") to any cell that represents the day of the week that you want to count for. 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 for weekdays 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 ("D16") in the VBA code.
Date and Count Range: This column and row 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 "5" to "11". These column and row references can be changed directly in the VBA code.
Day of the week: Select the day of the week by changing the cell reference ("C16") to any cell that represents the day of the week that you want to count for. 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 for weekdays 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.
ADDITIONAL NOTES
Note 1: This VBA example only focuses on the result in cell ("D16"), which counts the number of dates that fall on Wednesday. However, the image that is illustrated in this tutorial applies across multiple cells.
Note 1: This VBA example only focuses on the result in cell ("D16"), which counts the number of dates that fall on Wednesday. However, the image that is illustrated in this tutorial applies across multiple cells.
METHOD 3. Count cells by weekdays for each weekday using VBA
VBA
Sub Count_cells_by_weekdays()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'specify the row that captures the weekday for which to count and the row where to return the output
For y = 14 To 18
For y = 14 To 18
counter = 0
'count cells with the date that represents each weekday
For x = 5 To 11
For x = 5 To 11
If Weekday(ws.Range("B" & x), 2) = ws.Range("C" & y) Then
counter = counter + 1
End If
Next x
ws.Range("D" & y) = counter
Next y
End Sub
ADJUSTABLE PARAMETERS
Output Range: The output row and column range references are represented separately in the VBA code. The output row reference is represented by "y", which has been assigned with values between "14" and "18", and the output column reference is represented by "B". These references can be changed 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 "5" to "11". These column and row references can be changed directly in the VBA code.
Day of the week: The day of the week are in range ("C14:C18"). The row and column references are separately represented in the VBA code. The row reference is represented by "y", which has been assigned with values between "14" and "18". This is the same as the output row range reference. The column reference is represented by "D". These references can be changed 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 for weekdays 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: The output row and column range references are represented separately in the VBA code. The output row reference is represented by "y", which has been assigned with values between "14" and "18", and the output column reference is represented by "B". These references can be changed 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 "5" to "11". These column and row references can be changed directly in the VBA code.
Day of the week: The day of the week are in range ("C14:C18"). The row and column references are separately represented in the VBA code. The row reference is represented by "y", which has been assigned with values between "14" and "18". This is the same as the output row range reference. The column reference is represented by "D". These references can be changed 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 for weekdays 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 cells by weekdays using an Excel formula and VBA.
This tutorial provides one Excel method that can be applied to count values by weekdays in a selected range by using Excel SUMPRODUCT and WEEKDAY functions. In this example, as per the image above, we are counting the occurrence of all the days in a weekday (Monday to Friday) from range (B5:B11).
This tutorial provides three VBA methods that can be applied to count values by weekdays 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 a single weekday in a selected range. This is achieved by looping through each of the cells in the specified range that represents the dates. The third method returns the number of occurrence for each weekday by using the Weekday function and looping through the range of dates and through each cell that represents each weekday.
FORMULA
=SUMPRODUCT(--(WEEKDAY(range,2)=day_of_the_week))
=SUMPRODUCT(--(WEEKDAY(range,2)=day_of_the_week))
ARGUMENTS
range: The range of dates that you want to count from.
day_of_the_week: The day of the week that you want to count for. In this example we are counting by individual weekday, therefore, we use numbers 1 to 5, which corresponds to the return type (2).
range: The range of dates that you want to count from.
day_of_the_week: The day of the week that you want to count for. In this example we are counting by individual weekday, therefore, we use numbers 1 to 5, which corresponds to the return type (2).
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Count cells if weekdays | How to count all cells that have a weekday 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 |