Count by month and year
This tutorial shows how to count number of occurrences by month and year through the use of an Excel formula or VBA
Example: Count by month and year
|
=SUMPRODUCT((MONTH(B9:B15)=C5)*(YEAR(B9:B15)=C6))
|
This formula uses the Excel SUMPRODUCT, MONTH and YEAR functions to count the number of occurrences by month and year. The MONTH and YEAR functions, within the SUMPRODUCT function, are used to identify the occurrences of the relevant month and year from the list of dates and the SUMPRODUCT function sums these occurrences.
|
Sub Count_by_Month_and_Year()
'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("F8")
Set output = ws.Range("F8")
monthvalue = ws.Range("C5")
yearvalue = ws.Range("C6")
yearvalue = ws.Range("C6")
counter = 0
'count the number of occurances by month and year
For x = 9 To 15
For x = 9 To 15
If Month(ws.Range("B" & x)) = monthvalue And Year(ws.Range("B" & x)) = yearvalue 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 ("F8") 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.
Month and Year: Select the month and year that you want to count by changing cell references ("C5") and ("C6"), respectively, in the VBA code.
Worksheet Selection: Select the worksheet which captures a range of dates from which you want to count by month and year 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 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.
Month and Year: Select the month and year that you want to count by changing cell references ("C5") and ("C6"), respectively, in the VBA code.
Worksheet Selection: Select the worksheet which captures a range of dates from which you want to count by month and year 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 month and year through the use of an Excel formula or VBA.
The Excel method uses a combination of Excel SUMPRODUCT, MONTH and YEAR functions to count by month and year. The MONTH and YEAR functions are used to identify the nominated month and year from a range of dates and the SUMPRODUCT then sums all of the occurrences.
The VBA method uses the MONTH and YEAR VBA functions and loops through the range of dates to identify which of the dates contain the relevant month and year. If a date contains both the selected month and year 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((MONTH(date_range)=month_value)*(YEAR(date_range)=year_value))
=SUMPRODUCT((MONTH(date_range)=month_value)*(YEAR(date_range)=year_value))
ARGUMENTS
date_range: The range of cells that contain the dates.
month_value: The value that represents the month that you want to count for.
year_value: The value that represents the year that you want to count for.
date_range: The range of cells that contain the dates.
month_value: The value that represents the month that you want to count for.
year_value: The value that represents the year 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 | |
| Sum values by day | How to sum the values by day using Excel and VBA methods | |
| Count cells by weekdays | How to count cells by weekdays using Excel and VBA methods | |
| Sum values by month and year | How to sum values by month and year 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 | |
| MONTH Function | The Excel MONTH function returns the month from a specified date | |
| YEAR Function | The Excel YEAR function returns the year from a specified date |