Count by month
This tutorial shows how to count number of occurrences by month through the use of an Excel formula or VBA
Example: Count by month
|
=SUMPRODUCT((MONTH(B8:B14)=C5)*(MONTH(B8:B14)=C5))
|
This formula uses the Excel SUMPRODUCT and MONTH functions to count the number of occurrences by month . The MONTH function, within the SUMPRODUCT function, is used to identify the occurrences of the relevant month from the list of dates and the SUMPRODUCT function sums these occurrences.
|
Sub Count_by_Month()
'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")
monthvalue = ws.Range("C5")
counter = 0
'count the number of occurrences by month
For x = 8 To 14
For x = 8 To 14
If Month(ws.Range("B" & x)) = monthvalue 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.
Month: Select the month 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 month 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.
Month: Select the month 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 month 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 through the use of an Excel formula or VBA.
The Excel method uses a combination of Excel SUMPRODUCT and MONTH functions to count by month. The MONTH function is used to identify the nominated month from a range of dates and the SUMPRODUCT then sums all of the occurrences.
The VBA method uses the MONTH VBA function and loops through the range of dates to identify which of the dates contain the relevant month. If a date contains the specified month 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)*(MONTH(date_range)=month_value))
=SUMPRODUCT((MONTH(date_range)=month_value)*(MONTH(date_range)=month_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.
date_range: The range of cells that contain the dates.
month_value: The value that represents the month 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 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 |