Count by day
This tutorial shows how to count number of occurrences by day through the use of an Excel formula or VBA
Example: Count by day
|
=SUMPRODUCT((DAY(B8:B14)=C5)*(DAY(B8:B14)=C5))
|
This formula uses the Excel SUMPRODUCT and DAY functions to count the number of occurrences by day. The DAY function, within the SUMPRODUCT function, is used to identify the occurrences of the relevant day from the list of dates and the SUMPRODUCT function sums these occurrences.
|
Sub Count_by_Day()
'declare variables
Dim ws As Worksheet
Dim output As Range
Dim ws As Worksheet
Dim output As Range
Set ws = Worksheets("Analysis")
Set output = ws.Range("F7")
Set output = ws.Range("F7")
dayvalue = ws.Range("C5")
counter = 0
'count the number of occurrences by day
For x = 8 To 14
For x = 8 To 14
If Year(ws.Range("B" & x)) = dayvalue Then
counter = counter + 1
End If
Next x
output = counter
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("F7") in the VBA code.
Date Range: Select the range that captures the dates by changing the column reference ("B") in the VBA code. If you want to change the rows of the range, you will need to change the From and To values that are associated with the x variable.
Day: Select the day that you want to count by changing cell reference ("C5") in the VBA code or the value that represents the day in cell ("C5").
Worksheet Selection: Select the worksheet which captures a range of dates from which you want to count by day 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 ("F7") in the VBA code.
Date Range: Select the range that captures the dates by changing the column reference ("B") in the VBA code. If you want to change the rows of the range, you will need to change the From and To values that are associated with the x variable.
Day: Select the day that you want to count by changing cell reference ("C5") in the VBA code or the value that represents the day in cell ("C5").
Worksheet Selection: Select the worksheet which captures a range of dates from which you want to count by day 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 the number of occurrences by day through the use of an Excel formula or VBA.
The Excel method uses a combination of Excel SUMPRODUCT and DAY functions to count by day. The DAY function is used to identify the nominated day from a range of dates and the SUMPRODUCT then sums all of the occurrences.
The VBA method uses the DAY VBA function and loops through the range of dates to identify which of the dates contain the relevant day. If a date contains the specified day the VBA code will add 1 to the counter and once it has looped through the entire range it will return the total number of occurrences.
FORMULA
=SUMPRODUCT((DAY(date_range)=day_value)*(DAY(date_range)=day_value))
=SUMPRODUCT((DAY(date_range)=day_value)*(DAY(date_range)=day_value))
ARGUMENTS
date_range: The range of cells that contain the dates.
day_value: The value that represents the day that you want to count for.
date_range: The range of cells that contain the dates.
day_value: The value that represents the day that you want to count for.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Sum values by month | How to sum the values by month using Excel and VBA methods | |
| Sum values by year | How to sum the values by year using Excel and VBA methods | |
| Count by month and year | How to count number of occurrences by month and year using Excel and VBA methods | |
| Count by month | How to count number of occurrences by month using Excel and VBA methods | |
| Count by year | How to count number of occurrences by day 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 | |
| DAY Function | The Excel DAY function returns the day from a specified date |