We often do some summary or statistic at the end of one month or one year. In these summary tables, there are at least two columns, one column records the date, and the other column records the sales or product number or total costs etc. If we want to **calculate the total values based on a certain month of a yea**r, for example if we want to calculate the total sales for January 2020, how can we do? This article will help you to solve this problem.

See the example below. A column lists some dates. B column lists the sales. E column lists the month and year we want to calculate the total sales for. F column is used for showing the returned value by formula.

Table of Contents

## 1. Sum Values Based on Month and Year by SUMIF Function

**Step 1: **In cell F2, enter the formula:

` =SUMIFS(B2:B16,A2:A16,">="&DATE(2019,1,1),A2:A16,"<="&DATE(2019,1,31))`

In **SUMIFS** function, **B2:B16** is the sum range, **A2:A16** is the criteria range. **“>=”&DATE(2019,1,1) **and “**<=”&DATE(2019,1,31) **are the two criteria. Details please see below screenshot.

Verify that total sales **1500 **is displayed for January **2019 **after calculation.

**Step 2: **If we want to calculate total sales based on a certain period, we can change the parameters in **DATE** function. For example, to calculate the total sales for period 1/3/2019 – 1/3/2020, we can enter the formula:

` =SUMIFS(B2:B16,A2:A16,">="&DATE(2019,1,3),A2:A16,"<="&DATE(2020,1,3))`

Above all, you can change the date in **DATE function **to do sum per your demands.

## 2. Sum Values Based on Month and Year by SUMPRODUCT

**Step 1: **In cell F2, enter the formula

`=SUMPRODUCT((MONTH(A2:A16)=1)*(YEAR(A2:A16)=2019)*(B2:B16))`

In this formula we also use **MONTH** function and **YEAR** function to filter date from range A2:A16 based on criteria ‘Date is included in period January 2019’. **B2:B16** is the **sum range**.

**Step 2:** Click **Enter **to get the result. Verify that we get the same result in method #1 step#1.

## 3. Sum Values Based on Month and Year with User Defined Function (VBA Code)

If you want to sum values based on Month and Year with User Defined Function in Excel, you can use these steps:

**Step1:** Open the Visual Basic Editor by pressing **Alt + F11**.

**Step2:** In the Visual Basic Editor, click on “**Insert**” from the menu bar and select “**Module**” to create a new module.

**Step3:** In the new module, enter the following code, save the module with a suitable name.

```
Function SumValuesByMonthAndYear(sumRange As Range, dateRange As Range, sumMonth As Integer, sumYear As Integer) As Double
Dim i As Integer
Dim sum As Double
For i = 1 To sumRange.Rows.Count
If Month(dateRange.Cells(i, 1).Value) = sumMonth And Year(dateRange.Cells(i, 1).Value) = sumYear Then
sum = sum + sumRange.Cells(i, 1).Value
End If
Next i
SumValuesByMonthAndYear = sum
End Function
```

**Step4:** go back to your Excel worksheet and enter the following formula without the quotes into a blank cell.

`=SumValuesByMonthAndYear(B2:B16, A2:A16, 1, 2019)`

This function takes four arguments:

**sumRange**– The range of cells containing the values to sum**dateRange**– The range of cells containing the dates to check for the month and year**sumMonth**– The month to sum for (as an integer from 1 to 12)**sumYear**– The year to sum for (as a four-digit integer)

The function then loops through the **dateRange**, checks if each date matches the **sumMonth** and **sumYear**, and adds the corresponding value from the **sumRange** to the **sum** variable.

Finally, the function returns the sum variable as the result.

## 4. Video: Sum Values Based on Month and Year

This video will demonstrate how to Sum Values Based on Month and Year in Excel using the Formula and VBA Code.

## 5. Related Functions

- Excel SUMPRODUCT function

The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:**=**SUMPRODUCT (array1,[array2],…)… - Excel MONTH Function

The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12.The syntax of the MONTH function is as below:=MONTH (serial_number)… - Excel DATE function

The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)… - Excel YEAR function

The Excel YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)…