How to Do SUMIF by Month and Year in Excel (7 Quick Ways)

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for some of the easiest ways to do SUMIF by month and year, then you are in the right place. For calculating records of sales or expenditures or these types of calculations based on a month of a year Excel is very helpful.
To do these tasks in Excel you have to follow some methods so that you can do them effectively. After going through this article, you will be able to sum up values by month and year easily.
Additionally, I have attached an overview image to increase your understanding of what I’m going to explain in this article.

How to Do SUMIF by Month and Year in Excel


SUMIF by Month and Year: 7 Quick Ways

In the following data table, I have some records of sales based on some dates in Excel. To add up the sales based on a month of a year I will use the following example to explain the possible methods. Here, the date format is mm-dd-yyyy. I have used Microsoft Excel 365 version for this calculation. However, you can use any other version at your convenience.

Dataset to Do SUMIF by Month and Year in Excel


Method-1: Use of SUMIFS Function to Do SUMIF by Month and Year

If you want to add the sales of January 2019 then you can use the SUMIFS function and the DATE function.

Finding Total Sales Based on Specific Month

Now, follow the steps given below.

Steps:
➤ Firstly, select the output Cell G10.
➤ Secondly, type the following formula:

=SUMIFS(D5:D14,C5:C14,">="&DATE(2019,1,1),C5:C14,"<="&DATE(2019,1,31))

Combining SUMIFS & DATE Functions

Formula Breakdown

  • Here, D5:D14 is the range of Sales, and C5:C14 is the criteria range.
  • ">="&DATE(2019,1,1) is the first criterion where DATE will return the first date of a month.
  • "<="&DATE(2019,1,31) is the second criterion where DATE will return the last date of a month.

➤ Then, press ENTER.

Result:
Now, you will get the sum of sales for January 2019.

How to Do Sum Based on Month and Year Using Excel Functions


Method-2: Using SUMIFS Function to Sum up Values Based on a Certain Period

You can get the sum of sales for a certain period like from January 2019 to February 2020 by using the SUMIFS function and the DATE function.

Steps:
➤ Firstly, select the output Cell G10.
➤ Secondly, type the following formula:

=SUMIFS(D5:D14,C5:C14,">="&DATE(2019,1,1),C5:C14,"<="&DATE(2020,2,29))

Using SUMIFS Function for a Certain Period

Formula Breakdown

  • Here, D5:D14 is the range of Sales, and C5:C14 is the criteria range.
  • ">="&DATE(2019,1,1) is the first criterion where DATE will return the first date of a period.
  • "<="&DATE(2020,2,29) is the second criterion where DATE will return the last date of a period.

➤ Subsequently, press ENTER.

Result:
Then, you will get the sum of sales for the period of January 2019 to February 2020.

Finding Total Sales Between Two Dates

Read More: How to Use SUMIF in Date Range and Month in Excel


Method-3: Combining SUMIFS & EOMONTH Functions

You can get the sum of sales for a month or a year using the SUMIFS function and the EOMONTH function.

Steps:
➤ First, select the output Cell H7.
➤ Then, type the following formula:

=SUMIFS($D$5:$D$14,$C$5:$C$14,">="&G7,$C$5:$C$14,"<="&EOMONTH(G7,0))

Merging SUMIFS & EOMONTH Functions in Excel

Formula Breakdown

  • $D$5:$D$14 is the range of Sales, $C$5:$C$14 is the criteria range.
  • ">="&G7 is the first criterion where G7 is the first date of a month.
  • "<="&EOMONTH(G7,0) is the second criterion where EOMONTH will return the last date of a month.

➤ After that, press ENTER.
➤ Then, drag down the Fill Handle icon.

Using Fill Handle Icon

Result:
Then, you will get the sum of sales for different months of the year.

Use of SUMIFS & EOMONTH Functions to Get Total Sales


Method-4: Merging SUMPRODUCT, MONTH & YEAR Functions to Add Values

If you want to get the sum of sales for a month of a year then you can use the SUMPRODUCT function, the MONTH function, and the YEAR function.

Steps:
➤ Firstly, select the output Cell G10.
➤ Secondly, type the following formula:

=SUMPRODUCT((MONTH(C5:C14)=2)*(YEAR(C5:C14)=2020)*(D5:D14))

Combining SUMPRODUCT, MONTH & YEAR Functions in Excel

Formula Breakdown

  • Here, D5:D14 is the range of Sales, and C5:C14 is the range of Dates.
  • Then, MONTH(C5:C14) will return the months of the dates, and then it will be equal to 2 and which means February.
  • Similarly, YEAR(C5:C14) will return the years of the dates, and then it will be equal to 2020.

➤ Consequently, press ENTER.

Result:
Then, you will get the sum of sales for February of the year 2020.

Doing SUMIF with MONTH and YEAR Functions


Method-5: Summing up Values for a Month of a Year Based on Criteria

Let’s say, you want to sum up the Sales for January of the year 2019 for the criteria of the Region of North. You can do this by using the SUMIFS function and the DATE function.

Steps:
➤ Firstly, select the output Cell H9.
➤ Secondly, type the following formula:

=SUMIFS(D5:D14,E5:E14,"North",C5:C14,">="&DATE(2019,1,1),C5:C14,"<="&DATE(2019,1,31))

Uniting SUMIFS and DATE Functions for Two Criteria

Formula Breakdown

  • Here, D5:D14 is the range of Sales, E5:E14 is the first criteria range and North is the first criterion.
  • Then, C5:C14 is both the second and third criteria range.
  • ">="&DATE(2019,1,1) is the second criterion where DATE will return the first date of a period.
  • "<="&DATE(2019,1,31) is the third criterion where DATE will return the last date of a period.

➤ Lastly, press ENTER.

Result:
Now, you will get the sum of sales for January 2019 for the North Region.

Finding Total Sales Based on Both Region and Order Date


Method-6: Using SUM & IF Functions for a Month of a Year Based on Criteria

If you want to sum up the Sales for January of the year 2019 for criteria of the Region of North. Actually, you can do this by using the SUM function and the IF function.

Steps:
➤ Firstly, select the output Cell H9.
➤ Secondly, type the following formula:

=SUM(IF(MONTH(C5:C14)=1,IF(YEAR(C5:C14)=2019,IF(E5:E14="North",D5:D14))))

Using the IF function three logical conditions have been used here.

Combining SUM, IF, MONTH & YEAR Functions

➤ Finally, press ENTER.

Result:
Afterward, you will get the sum of sales for January 2019 for the North Region.

How to Do SUMIF by Month and Year Using Combined Functions

Read More: Sum Values Based on Date in Excel


Method-7: Using Pivot Table for Doing SUMIF by Month and Year

You can have the sum of sales for a month or a year using the Pivot Table.

Steps:
➤ At first, go to Insert Tab >> PivotTable Option >> From Table/Range.

Inserting Pivot Table to SUMIF Sales by Month and Year

As a result, PivotTable from table or range Dialog Box will pop up.

➤ Then, select the table/range >> After that, click on New Worksheet.
➤ Lastly, press OK.

Select Data for Pivot Table

At this time, a new sheet will appear where you have two portions named PivotTable1 and PivotTable Fields.

Generated Pivot Table Fields

➤ Now, drag down Sales to the Values area >> Years to the Rows >> Date to the Rows area.

Creating Pivot Table by Dragging Elements

After that, the following table will be created.

➤ Then, select any Cell of the Row Labels.
➤ Now, Right-click on your mouse >> from the Context Menu Bar >> select Group Option.

Use of Context Menu Bar in Pivot Table

As a result, a Grouping Dialog Box will appear.

➤ Now, check the Starting at and Ending at the desired dates and mark the Months and Years option in the indicated area.
➤ Then, press OK.

Set Criteria for Grouping Dialog Box

Result:
Now, you will get the sum of sales for a month of a year as below.

SUMIF by Month and Year with Pivot Table


Practice Section

For doing practice by yourself we have provided a Practice section like below for each method in each sheet on the right side. Please do it by yourself.

Practice Section to Do SUMIF by Month and Year in Excel


Download Practice Workbook


Conclusion

In this article, I tried to cover the easiest ways to do SUMIF by Month and Year in Excel effectively. Hope you will find it useful. If you have any suggestions or questions, feel free to share them with us.


<< Go Back to SUMIF Date Range | Excel SUMIF Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo