Sum values by year

To sum the values by year we can apply multiple Excel and VBA methods

Example: Sum values by year

METHOD 1. Sum values by year using SUMIFS and DATE functions

EXCEL

=SUMIFS(C12:C18,B12:B18,">="&DATE(C5,C7,C6),B12:B18,"<="&DATE(C5,C9,C8))

The formula uses a combination of the Excel SUMIFS and DATE functions to sum the values associated with the specific year. To sum the values associated with the whole year we need to specify the first and last date of the year, which is driven by the two Excel DATE functions. The formula sums all of the values within the specified dates.

METHOD 2. Sum values by year using SUMPRODUCT and YEAR functions

EXCEL

=SUMPRODUCT((YEAR(B12:B18)=C5)*C12:C18)

The formula uses a combination of the Excel SUMPRODUCT and YEAR functions to sum the values associated with the specific year. With this formula you only have to select the data (dates and associated values) and the specific year for which you want to sum the values.

METHOD 3. Sum values by year using an Array formula with SUM, IF and YEAR functions

EXCEL

{=SUM(IF(YEAR(B12:B18)=C5,C12:C18,0),0)}

This is an array formula that uses a combination of the Excel SUM, IF and YEAR functions to sum the values associated with the specific year. Given this is an array formula, after entering the formula into a cell you need to click Ctrl + Shift + Enter to make it an array formula.

METHOD 1. Sum values by year using VBA with a For Loop

VBA

Sub Sum_values_by_year()

'declare a variable
Dim ws As Worksheet

Set ws = Worksheets("Analysis")
Set syear = ws.Range("C5")

'sum values by year using the For Loop
For i = 12 To 18

If Year(ws.Cells(i, 2)) = syear Then

sumyears = sumyears + ws.Cells(i, 3).Value

End If

Next i

ws.Range("F11") = sumyears

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.

PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.
Dates: If using the exact VBA code you will need to capture the dates that you are testing against in range ("B12:B18"). This is especially important for the For Loop to loop through specified rows and columns. In this example we have specified for the For Loop to loop through rows 12 to 18 in column 2 to find all of the dates associated with year 2017.
Values: If using the exact VBA code you will need to capture the values that you want to sum that are associated with the specified year in range ("C12:C18"), which is the column next to the dates. This is especially important for the For Loop to loop through specified rows and columns. In this example we have specified for the For Loop to loop through rows 12 to 18 in column 3 to find all of the values associated with the dates that fall into year 2017.

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("F11") in the VBA code to any cell in the worksheet that doesn't conflict with the formula.
Date: Select the range that captures the dates that you want to test against by changing the i values that represent the row range and the value of 2 in the Cells reference that represents the column reference. In this example the dates are captured in rows 12 to 18 and column 2, therefore, if you are capturing the dates in different rows you will need to change the From and To i values in the VBA code. If you are capturing the dates in a different column you will also need to change the value of 2 in the Cells reference, to the column that captures the dates.
Values: Select the range of values that you want to sum that are associated with the specified year by changing the i values that represent the row range and the value of 3 in the Cells reference that represents the column reference. In this example the dates are captured in rows 12 to 18 (this example assumes that the dates and values are captured in the same row range, therefore, the row range for both dates and values need to match) and column 3, therefore, if you are capturing the values in different rows you will need to change the From and To i values in the VBA code (this will be the same as for dates, therefore, if you have already changed the i values for the dates no further action is required for the values row range). If you are capturing the values in a different column you will also need to change the value of 3 in the Cells reference, to the column that captures the values.

METHOD 2. Sum values by year using VBA with a formula for SUMIFS and DATE functions

VBA

Sub Sum_values_by_year()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'sum values by year using formula
ws.Range("F11").Formula = "=SUMIFS(C12:C18,B12:B18,"">=""&DATE(C5,C6,C7),B12:B18,""<=""&DATE(C5,C9,C8))"

End Sub

PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.
Dates: If using the exact VBA code you will need to capture the dates that you are testing against in range (B12:B18).
Values: If using the exact VBA code you will need to capture the values that you want to sum that are associated with the specified year in range (C12:C18), which is the column next to the dates.

METHOD 3. Sum values by year using VBA with a formula for SUMPRODUCT and YEAR functions

VBA

Sub Sum_values_by_year()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'sum values by year using formula
ws.Range("F11").Formula = "=SUMPRODUCT((YEAR(B12:B18)=C5)*C12:C18)"

End Sub

PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.
Dates: If using the exact VBA code you will need to capture the dates that you are testing against in range (B12:B18).
Values: If using the exact VBA code you will need to capture the values that you want to sum that are associated with the specified year in range (C12:C18), which is the column next to the dates.

METHOD 4. Sum values by year using VBA with a array formula

VBA

Sub Sum_values_by_year()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'sum values by year using formula
ws.Range("F11").FormulaArray = "=SUM(IF(YEAR(B12:B18)=C5,C12:C18,0),0)"

End Sub

PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.
Dates: If using the exact VBA code you will need to capture the dates that you are testing against in range (B12:B18).
Values: If using the exact VBA code you will need to capture the values that you want to sum that are associated with the specified year in range (C12:C18), which is the column next to the dates.

Explanation about the formula used to sum values by year

EXPLANATION

EXPLANATION
To sum the values by year we can apply multiple Excel and VBA methods.

FORMULAS
=SUMIFS(values,dates,">="&DATE(year,first_month_of_year,first_day_of_year),dates,"<="&DATE(date,last_month_of_year,last_day_of_year))
=SUMPRODUCT((YEAR(dates)=year)*values)
{=SUM(IF(YEAR(dates)=year,values,0),0)}

ARGUMENTS
dates: A range of dates to be tested for the specific year.
values: A range of values associated with dates that are to be summed.
year: The year the dates are tested against to return the sum of associated values.
first_day_of_year: Numeric value of the first day of the year, which is 1.
first_month_of_year: Numeric value of the first month of the year, which is 1.
last_day_of_year: Numeric value of the last day of the year, which is 31.
last_month_of_year: Numeric value of the last month of the year, which is 12.