# How to Sum Values Based on Month and Year in Excel

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

**Method 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.

**Method 2: Sum Values Based on Month and Year by SUMPRODUCT**

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

**=SUMPRODUCT((MONTH(A2:A16)=1)*(12(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.

### 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)…