Sum values by month and year
This tutorial shows how to sum values by month and year through the use of an Excel formula or VBA
Example: Sum values by month and year
|
=SUMPRODUCT(--(MONTH(B9:B15)=C5),--(YEAR(B9:B15)=C6),C9:C15)
|
This formula uses the Excel SUMPRODUCT, MONTH and YEAR functions to sum numbers 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 is used to sum the associated numbers.
|
Sub Sum_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
'sum value 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 + ws.Range("C" & x)
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. Please note that in this example both the data and sum ranges use x as the row variable.
Sum Range: Select the range from which you want to sum value by changing the column reference ("C") 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. Please note that in this example both the data and sum ranges use x as the row variable.
Month and Year: Select the month and year that you want to sum for by changing cell references ("C5") and ("C6"), respectively, in the VBA code.
Worksheet Selection: Select the worksheet which captures all of the required information to sum values 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. Please note that in this example both the data and sum ranges use x as the row variable.
Sum Range: Select the range from which you want to sum value by changing the column reference ("C") 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. Please note that in this example both the data and sum ranges use x as the row variable.
Month and Year: Select the month and year that you want to sum for by changing cell references ("C5") and ("C6"), respectively, in the VBA code.
Worksheet Selection: Select the worksheet which captures all of the required information to sum values 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 sum values 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 sum the values 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 relevant values against the relevant dates that contain the month and year that has been selected.
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 sum the value of the cell in the next column.
FORMULA
=SUMPRODUCT(--(MONTH(date_range)=month_value),--(YEAR(date_range)=year_value),sum_range)
=SUMPRODUCT(--(MONTH(date_range)=month_value),--(YEAR(date_range)=year_value),sum_range)
ARGUMENTS
date_range: The range of cells that contain the dates.
sum_range: The range of cells that contain values that you want to sum.
month_value: The value that represents the month that you want to sum for.
year_value: The value that represents the year that you want to sum for.
date_range: The range of cells that contain the dates.
sum_range: The range of cells that contain values that you want to sum.
month_value: The value that represents the month that you want to sum for.
year_value: The value that represents the year that you want to sum 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 | |
| Sum values if greater than a specific month | How to sum the values if greater than a specific month using Excel and VBA methods | |
| Sum values if greater than a specific year | How to sum the values if greater than a specific 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 |