Count cells if weekends
This tutorial shows how to count all cells that have a weekend through the use of an Excel formula or VBA
Example: Count cells if weekends
|
=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 to count the number of times any of the weekends (Saturday and Sunday) appears in the selected range. The formula is counting all of the occurrences of Saturday and Sunday by using the >=C5 as the day of the week (C5 being 6). The 6 represents Saturday, therefore the formula counts all of the amounts that relate to Saturday and Sunday.
|
Sub Count_cells_if_weekends()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'count cells if weekends
ws.Range("E8").Formula = "=SUMPRODUCT(--(WEEKDAY(B8:B14,2)>=C5))"
ws.Range("E8").Formula = "=SUMPRODUCT(--(WEEKDAY(B8:B14,2)>=C5))"
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("E8") 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 first day of the weekend, in this example being a Saturday (value of 6) given we are counting only the days in a weekend, by changing cell ("C5") in the VBA code. 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.
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 weekend 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 ("E8") 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 first day of the weekend, in this example being a Saturday (value of 6) given we are counting only the days in a weekend, by changing cell ("C5") in the VBA code. 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.
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 weekend 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 cells if weekends using VBA
VBA
Sub Count_cells_if_weekends()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
counter = 0
'count cells if weekends
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("E8") = counter
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("E8") 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 first day of the weekend (Saturday) 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 weekend 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 ("E8") 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 first day of the weekend (Saturday) 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 weekend 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 weekend using an Excel formula and VBA.
This tutorial provides one Excel method that can be applied to count the number of cells that capture the day of a weekend 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 weekend (Saturday to Sunday) in range (B8:B14).
This tutorial provides two VBA methods that can be applied to count number of cells that capture the day of a weekend 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 days in a weekend (Saturday to Sunday) 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)>=first_day_of_the_weekend))
=SUMPRODUCT(--(WEEKDAY(date_count_range,2)>=first_day_of_the_weekend))
ARGUMENTS
date_count_range: The range of dates that you want to count.
first_day_of_the_weekend: The first day of the weekend. In this example we are counting 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).
date_count_range: The range of dates that you want to count.
first_day_of_the_weekend: The first day of the weekend. In this example we are counting 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).
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 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 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 |