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 year, 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.

Sum Values Based on Month 1

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.

Sum Values Based on Month 2

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

Sum Values Based on Month 3

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)).

Sum Values Based on Month 4

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.

Sum Values Based on Month 5

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

Sum Values Based on Month 6

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

 

Related Posts

Add Months To Date In Excel

It is important to adjust time periods when performing financial modeling. In Excel, you can use the MONTH function to add a specific number of months to a date. The MONTH function takes one argument: the number of months you ...

Add Days to Date in MS Excel

If you are an vavid MS Excel user, then you might have come across situations where you need to add the same or different days into the particular date, and if you have done this task manually, then let me ...

Filter Data By Date Field

The use of Microsoft Excel's Format Cells and Auto Filter option is a great way to filter data by year or Month. This article will discuss how to filter by date field using Filter function and Format cells in Microsoft ...

How to Count Occurrences in Entire Workbook in Excel

This post will guide you how to count number of occurrences in whole workbook in Excel 2013/2016 or Excel office 365. How do I count how many times a string is repeated in all worksheets in Excel. You may be ...

How to Count Dates of Given Year in Excel

This post will guide you how to count Dates of a certain year in the range of dates using a formula in Excel 2013/2016 or Excel office 365. How do I count dates by a given year in Excel. And ...

Sidebar