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

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.

Download Workbook

7 Ways to Do SUMIF by Month and Year

In the following data table, I have some records of sales based on some dates. 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. You can use any other versions as per your convenience.

dataset

Method-1: Using 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.

using SUMIFS for a month of a year

Step-01:
➤Select the output Cell G5
➤Type the following formula

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

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

using SUMIFS for a month of a year

➤Press ENTER

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

using SUMIFS for a month of a year

Method-2: Using SUMIFS function for 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.

certain period

Step-01:
➤Select the output Cell G5
➤Type the following formula

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

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

certain period

➤Press ENTER

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

certain period

Method-3: Using SUMIFS function and EOMONTH function

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

using SUMIFS and EOMONTH

Step-01:
➤Select the output Cell G5
➤Type the following formula

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

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

using SUMIFS and EOMONTH

Step-02:
➤Press ENTER
➤Drag down the Fill Handle tool.

using SUMIFS and EOMONTH

➤Press ENTER

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

SUMIF by month and year

Method-4: Using SUMPRODUCT function

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.

SUMPRODUCT function

Step-01:
➤Select the output Cell G5
➤Type the following formula

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

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

SUMPRODUCT function

➤Press ENTER

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

SUMPRODUCT function

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

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

Sum up based on criteria

Step-01:
➤Select the output Cell G5
➤Type the following formula

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

D5:D14 is the range of Sales, E5:E14 is the first criteria range and C5:C14 is the second and third criteria range

Sum up based on criteria

North is the first criteria
">="&DATE(2019,1,1) is the second criteria where DATE will return the first date of a period.
"<="&DATE(2019,1,31) is the third criteria where DATE will return the last date of a period.

➤Press ENTER

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

Sum up based on criteria

Method-6: Using SUM and IF function for a Month of a Year Based on Criteria

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

using SUM and IF function

Step-01:
➤Select the output Cell G5
➤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.

using SUM and IF function

➤Press ENTER

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

using SUM and IF function

Method-7: Using Pivot Table

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

Pivot table

Step-01:
➤Go to Insert Tab>>PivotTable Option

Pivot table

Create PivotTable Dialog Box will pop up.

➤Select the table/range
➤Click on New Worksheet
➤Press OK

Pivot table

Then a new sheet will appear where you have two portions named PivotTable1 and PivotTable Fields

Pivot table

➤Drag down Date to the Rows area and Sales to the Values area.

Pivot table

After that, the following table will be created.

➤Select any Cell of the Row Labels

Pivot table

➤Right-click on your mouse.
➤Select Group Option

Pivot table

➤Click on the Months and Years option in the indicated area
➤Press OK

Pivot table

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

SUMIF by month and year

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

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.

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo