Count by year
This tutorial shows how to count number of occurrences by year through the use of an Excel formula or VBA
Example: Count by year
|
=SUMPRODUCT((YEAR(B8:B14)=C5)*(YEAR(B8:B14)=C5))
|
This formula uses the Excel SUMPRODUCT and YEAR functions to count the number of occurrences by year. The YEAR function, within the SUMPRODUCT function, is used to identify the occurrences of the relevant year from the list of dates and the SUMPRODUCT function sums these occurrences.
|
Sub Count_by_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("F7")
Set output = ws.Range("F7")
yearvalue = ws.Range("C5")
counter = 0
'count the number of occurrences by year
For x = 8 To 14
For x = 8 To 14
If 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 ("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.
Year: Select the year that you want to count by changing cell reference ("C5") in the VBA code.
Worksheet Selection: Select the worksheet which captures a range of dates from which you want to count by 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 ("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.
Year: Select the year that you want to count by changing cell reference ("C5") in the VBA code.
Worksheet Selection: Select the worksheet which captures a range of dates from which you want to count by 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 year through the use of an Excel formula or VBA.
The Excel method uses a combination of Excel SUMPRODUCT and YEAR functions to count by year. The YEAR function is used to identify the nominated year from a range of dates and the SUMPRODUCT then sums all of the occurrences.
The VBA method uses the YEAR VBA function and loops through the range of dates to identify which of the dates contain the relevant year. If a date contains the specified 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((YEAR(date_range)=year_value)*(YEAR(date_range)=year_value))
=SUMPRODUCT((YEAR(date_range)=year_value)*(YEAR(date_range)=year_value))
ARGUMENTS
date_range: The range of cells that contain the dates.
year_value: The value that represents the year that you want to count for.
date_range: The range of cells that contain the dates.
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 | |
| 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 | |
| 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 | |
| YEAR Function | The Excel YEAR function returns the year from a specified date |